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.
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.
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…
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.
Select the data source that you configured in the ODBC Data Source Administrator from the dropdown.
If asked for credentials, choose Default or Custom. You already configured the credentials when setting up the data source in Step 2.
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.
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.
That’s it! Please comment below with any questions you may have and share the tutorial if you found it helpful.