Connecting Excel to MySQL With the Official Connector

MySQL powers a large share of small business applications, which means a large share of the questions about getting that data into a spreadsheet. The answer used to be an Oracle-made Excel add-in, and that route has been retired. Today the practical route is Power Query with a MySQL connector. This post covers the two connectors, how to set up a safe user, how to connect and run a query, and the errors that appear most often. It is a spoke of connecting Excel directly to a database.

Two connectors, two routes

Power Query offers a MySQL database option under Get Data, From Database. It relies on MySQL Connector/NET, the .NET data provider from MySQL, which must be installed on the computer. Microsoft's connector documentation lists the supported versions; use a current 8.x release unless the page says otherwise.

The alternative is MySQL Connector/ODBC, an ODBC driver. With it, you use Get Data, From Other Sources, From ODBC and a data source, the same route as connecting Excel to PostgreSQL with ODBC. ODBC works where the .NET provider is difficult to install, and in the Data Model-heavy flows some people prefer it.

Whichever you choose, install the build that matches Excel: 64-bit for 64-bit Excel. Check under File, Account, About Excel.

The older MySQL for Excel add-in, which put an Export and Import Data panel in the ribbon, was discontinued, and the project documents its end of life. Do not build new processes on it, and plan to replace any workbook that still uses it.

Create a user for the workbook

Do not connect with root or with an application's own account. Create a user whose only power is reading what the report needs:

CREATE USER 'report_reader'@'10.0.%' IDENTIFIED BY 'set-from-a-password-manager';
GRANT SELECT ON shop.reporting_orders TO 'report_reader'@'10.0.%';
GRANT SELECT ON shop.reporting_customers TO 'report_reader'@'10.0.%';
ALTER USER 'report_reader'@'10.0.%' WITH MAX_USER_CONNECTIONS 3 MAX_QUERIES_PER_HOUR 2000;

The host part (10.0.%) limits where the user can connect from, so a leaked password is useless from elsewhere. SELECT on specific views gives the report the data and nothing else. MAX_USER_CONNECTIONS and the query limit stop a refresh-happy workbook from flooding the server. Create the views as the interface between the application tables and the reports, so a schema change does not break every workbook (giving Excel users a read only database account).

Connect with Power Query

In Excel: Data, Get Data, From Database, From MySQL Database. Enter the server (host, optionally with :port if not 3306) and the database. Open Advanced options and paste a SQL statement:

SELECT o.order_date, c.region, o.total
FROM reporting_orders o
JOIN reporting_customers c ON c.id = o.customer_id
WHERE o.order_date >= '2025-01-01';

Click OK, and on the credentials prompt choose the Database tab, enter the user and password and set the level to the server. If you saw the message "This connector requires one or more additional components to be installed", Connector/NET is missing or the wrong bitness.

The preview shows the first rows. Load To, Only Create Connection plus Add to Data Model suits pivots. Name the query, set refresh properties, and check the first rows against the database, as in building a pivot table from a database query.

Power Query tries to fold your steps into the SQL. When you filter or remove columns after a native query, folding may be turned off, so the filter happens in Excel after downloading everything. Put filters in the SQL statement for large tables.

Authentication and SSL errors

MySQL 8 changed the default authentication plugin to caching_sha2_password. A connector that predates it fails with messages such as "Authentication method 'caching_sha2_password' is not supported" or "Unable to connect to any of the specified MySQL hosts". The remedy is to update Connector/NET or Connector/ODBC to a version that supports it. Changing the user to the older mysql_native_password plugin works as a workaround, and MySQL has been deprecating that plugin, so treat it as temporary:

ALTER USER 'report_reader'@'10.0.%' IDENTIFIED WITH mysql_native_password BY 'set-from-a-password-manager';

SSL: if the server requires encrypted connections (require_secure_transport=ON), the connector must request SSL. In Power Query, the MySQL connector uses encryption when the server offers it, and the credential dialog has an Encrypt connection option. For ODBC, set SSLMODE=REQUIRED in the DSN. A certificate error means the client does not trust the server's certificate: install the CA certificate, or ask the administrator for the proper one. Do not turn off verification on a link that crosses the internet.

Other common failures:

The server's error log and SHOW GRANTS FOR 'report_reader'@'10.0.%'; show what the account may do.

Character sets and types

MySQL's utf8mb4 is the character set to use. If accents arrive garbled, check the database and column character sets, and that the connection uses utf8mb4. For ODBC, set CHARSET=utf8mb4 in the DSN.

Types to watch: TINYINT(1) is MySQL's boolean and arrives as 0 and 1; DECIMAL arrives as a decimal number and can lose precision if Excel converts it to a floating-point value with more than 15 digits (Excel number precision and the 15 digit limit); DATETIME has no time zone, so the values are shown as stored; the zero date 0000-00-00 that some old MySQL data contains causes conversion errors, so filter or cast it in the SQL (NULLIF(order_date, '0000-00-00') works only if the mode allows the comparison, so clean it in a view).

Check the connection is doing what you think

Run the same count and total in MySQL and in Excel. Check the user's grants. Open a trace of the server's general log briefly (on a test server) or SHOW PROCESSLIST during a refresh to see the statement Excel actually sends. If the statement is a bare SELECT * on a large table, the SQL box was not used. Look at the refresh time and the number of rows returned, and tune the query before you share the workbook.

Sharing the workbook

The workbook carries the server name, the database and the query, though not the stored credentials. A colleague who opens it needs the connector, a MySQL user and network access, and will be asked for credentials on first refresh. Document these steps beside the workbook. Avoid distributing one shared password: individual users, or a read-only role per team, make revocation and audit possible (keeping database credentials out of shared workbooks).

When connecting Excel to MySQL directly is a mistake

The database is the production primary. Heavy reports belong on a replica, so a refresh cannot slow the application.

The data is sensitive and the audience wide. Publish aggregates, not a connection.

You need the data on a schedule with no human. Use a script or a BI tool with service accounts and logging.

The workbook will be sent to outsiders. Cached rows and connection details leave with it.

See Microsoft's page on the MySQL database connector and the MySQL manual on creating users and granting privileges. To convert a small result to JSON, use the CSV to JSON converter.