Sure, Excel is used for spreadsheets, but did you know you can connect Excel to external data sources? In this article, we are going to discuss how to connect an Excel spreadsheet to a MySQL database table and use data from the database table to populate our spreadsheet. To prepare for this connection, you need to do a few things.
Preparation
First, you must download the most recent version of the Open Database Connectivity (ODBC) driver for MySQL. The current ODBC driver for MySQL can be found at
https: //dev.muscle.com/downloads/connector/odbc/
Make sure that after downloading the file, you compare the hashcode of the md5 file with the one listed on the download page.
Next, you will need to install the driver you just downloaded. Double click the file to start the installation process. After completing the installation process, you will need to create a Database Source Name (DSN) for use with Excel.
Create a DSN
The DSN will contain all the connection information required to use the MySQL database table. On Windows, you will need to click Start, then Control Panel, then Administrative Tools, and then Data Sources (ODBC). You should see the following information:
– /
Notice the tabs in the image above. A custom DSN is only available to the user who created it. The system DSN is available to anyone who can log on to the system. File DSN is a .DSN file that can be carried and used on other systems that have the same OS and drivers installed.
To proceed with DSN creation, click the Add button in the upper right corner.
You may have to scroll down to see the MySQL ODBC 5.x driver. If not, then something went wrong with the driver installation in the Prepare section of this publication. To proceed with DSN creation, make sure the MySQL ODBC 5.x driver is highlighted and click Finish. You should now see a window similar to the one below:
You will then need to provide the information required to complete the form shown above. The MySQL database and table we are using for this article are on a development machine and are used by only one person. For “production” environments, it is recommended that you create a new user and grant only SELECT privileges to him. In the future, if necessary, you can grant additional privileges.
After you have provided the details of the data source configuration, you must click the Test button to ensure everything is in working order. Then click OK. You should now see the data source name you specified in the form in the previous set, as specified in the ODBC Data Source Administrator window:
Create a spreadsheet connection
Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel. With Excel open, click the data ribbon. For newer versions of Excel, click Get Data, then From Other Sources, and then From ODBC.
In earlier versions of Excel, this is a slightly more process. First, you should see something like this:
The next step is to click on the Connections link located just below the word Data in the list of tabs. The location of the Connections link is circled in red in the image above. You should be presented with the Book Connections window:
The next step is to click on the “Add” button. The “Existing Connections” window will open:
Obviously you don’t want to work with any of the connections listed. Therefore, click the button “View more “. The data source selection window will open:
As with the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window. Hence, you want to double click on the + Connect to New Data Source.odc folder. In this case, you should see the Data Connection Wizard window:
Given the listed data source options, you want to highlight the ODBC DSN and click Next. The next step of the Data Connection Wizard will display all ODBC data sources available on the system you are using.
Hopefully, if all goes to plan, you will see the DSN created in the previous steps in the list of ODBC data sources. Highlight it and click Next.
The next step of the Data Connection Wizard is save and complete. The file name field should be automatically filled in for you. You can provide a description. The description used in the example is pretty self explanatory for anyone who might use it. Then click the Finish button in the lower right corner of the window.
You should now return to the workbook connection window. The data connection you just created should appear:
Import table data
You can close the book connection window. We need to click the Existing Connections button on the Excel data ribbon. The Existing Connections button should be on the left of the Data ribbon.
When you click on the “Existing Connections” button, you will see the “Existing Connections” window. You saw this window in the previous steps, now the difference is that your data connection must be listed at the top:
Make sure that the data connection you created in the previous steps is highlighted, and then click the Open button. You should now see the Import Data window:
For the purposes of this post, we are going to use the default settings in the Import Data window. Then click OK. If everything worked for you, you should now be presented with the MySQL database table data in your worksheet.
In this post, the table we worked with had two fields. The first field is an auto-increment INT field called ID. The second field is VARCHAR (50) and is called fname. Our final table looks something like this:
As you may have noticed, the first line contains the names of the table columns. You can also use the dropdown arrows next to the column names to sort the columns.
Conclusion
In this post, we covered where to find the latest MySQL ODBC drivers, how to create a DSN, how to create a spreadsheet data connection using a DSN, and how to use a spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!
–