Excel Import MySQL Table with ODBC Database Query

In this article, you will learn how to import a MySQL Table from the database into an Excel table using an ODBC Database Query. If you are looking to do the reverse and import an Excel table into any SQL database (including MySQL), try my other tutorial here. This tutorial should work on Microsoft Excel 2016 or greater with Microsoft Windows.

Step 1: Install MySQL Connector/ODBC

Firstly, you must install the MySQL Connector/ODBC software. This is the ODBC driver that will allow you to add a MySQL server as a data source. You can download the driver here: https://dev.mysql.com/downloads/connector/odbc/. It is important to match the architecture of the driver with the architecture of your Excel install. In my case, I have a 64-bit system but 32-bit Excel so I actually needed the 32-bit driver. You can check your Excel install by going to File > Account and clicking About Excel.

Screenshot showing Excel is a 32-bit install
About Excel showing 32-bit install

Step 2: Setup the MySQL Server as an ODBC Data Source

With MySQL Connector/ODBC installed, you need to configure the data source in Windows so we can use it in Excel. Open the ODBC Data Sources Administrator by searching for it in the start menu. If you are given the option of opening the 32-bit or 64-bit window, open the one that matches the driver you installed and your Excel install. In my case, this was the 32-bit manager. You should now see the driver listed under the Drivers tab.

Screenshot showing installed drivers in ODBC Data Source Administrator
ODBC Data Source Administrator showing driver installation.

You can add the new data source as either a System DSN or User DSN, with the former meaning it will be accessible to all users on the system and the latter being accessible only to the current user. To add as a User DSN, go to the User DSN tab and click Add…

Screenshot showing adding ODBC data source

Then, select the MySQL driver and click Finish to move the next step of adding the MySQL server details. Either the Unicode or ANSI driver should work, but if you require Unicode support ensure you use the Unicode variant.

Next, fill out the details for your server and test the connection. You may select a database at this stage if you wish, but you will have the opportunity to select it in Excel later if you leave the database field blank for the time being. Click OK to finalize the data source configuration and close the ODBC Data Source Administrator.

Step 3: Create the Excel Query

Open Excel and go to the Data tab. Click Get Data and choose From Other Sources > From ODBC.

Screenshot of choosing the menu option for ODBC data source

Select the data source that you configured in the ODBC Data Source Administrator from the dropdown.

Screenshot of choosing our ODBC data source

If asked for credentials, choose Default or Custom. You already configured the credentials when setting up the data source in Step 2.

Screenshot of configuring authentication details for Excel to use

Finally, click Connect to connect to the server. A list of tables will be loaded that you can choose to import into Excel. Select the table you want to import and click Load to load it in directly. If you need to modify the data, you can do so by clicking Transform Data. That will open the query editor allowing you to make changes that will be repeated each time the query refreshes. For more information on transforming data, please see the video tutorial at the top of this page.

Screenshot of selecting the MySQL table to import into Excel

With the query finished, you can make changes by clicking the table and clicking the Query tab to refresh or change query properties such as automatic refresh.

Screenshot of Excel showing imported MySQL table

That’s it! Please comment below with any questions you may have and share the tutorial if you found it helpful.

2 thoughts on “Excel Import MySQL Table with ODBC Database Query”

  1. I successfully used this tutorial, “Excel Function to Calculate Travel Time and Distance using Google Maps API with VBA”, but the Distance function seems to be limited by distance. I try to calculate distance of location of any significance and excel returns a #VALUE! error. Closer together distances seem to work fine, but anything of any distance gets the error. The time function seems to work fine. Any insight on how to remedy this?

    1. Hi Fred,

      Thanks for the comment. This has been a commonly reported issue in the YouTube comments for that video and the fix is to change meters from an Integer type to a type that can store a larger value. You can try specifying it as Variant instead.

Leave a Reply

Your email address will not be published. Required fields are marked *