Connecting Excel to MySQL

Populate your spreadsheet using a database

Sure Excel is used for spreadsheets, but did you know you can connect Excel to external data sources? In this article we’re going to discuss how to connect an Excel spreadsheet to a MySQL database table and use the data in the database table to populate our spreadsheet. There are a few things you need to do in order to prepare for this connection.

Preparation

First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL. The current ODBC driver for MySQL can be located at

https://dev.mysql.com/downloads/connector/odbc/

Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.

Next, you will need to install the driver you just downloaded. Double click the file to start the install process. Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.

Creating the DSN

The DSN will contain all of the connection information necessary to use the MySQL database table. On a Windows system, you will need to click onStart, thenControl Panel, thenAdministrative Tools, thenData Sources (ODBC). You should see the following information:

Notice the tabs in the image above. AUser DSNis only available to the user that created it. ASystem DSNis available to anyone that can log into the machine. AFile DSNis a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.

To continue creating the DSN, click on theAddbutton near the top right corner.

You will probably have to scroll down to see theMySQL ODBC 5.x Driver. If it’s not present, something went wrong with installing the driver in the Preparation section of this post. To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on theFinishbutton. You should now see a window similar to the one listed below:

Next you will need to supply the information necessary to complete the form shown above. The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only. In the future, you can grant additional privileges if necessary.

After you have supplied the details for your data source configuration, you should click on theTestbutton to make sure everything is in working order. Next, click on theOKbutton. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:

Creating the Spreadsheet Connection

Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel. Once you have opened Excel, click on theDataribbon. For newer versions of Excel, click onGet Data, thenFrom Other Sources, thenFrom ODBC.

In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:

The next step is to click on theConnectionslink located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:

The next step is to click on theAddbutton. This will present you with theExisting Connectionswindow:

Obviously you don’t want to work on any of the connections listed. Therefore, click on theBrowse for More…button. This will present you with theSelect Data Sourcewindow:

Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window. Therefore, you want to double click on the+Connect to New Data Source.odcfolder. In doing so, you should be now see theData Connection Wizardwindow:

Given the data source choices listed, you want to highlightODBC DSNand clickNext. The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.

Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources. Highlight it and click onNext.

The next step in the Data Connection Wizard is to save and finish. The file name field should be auto filled for you. You can supply a description. The description used in the example is pretty self explanatory for anyone that might use it. Next, click on theFinishbutton in the lower right of the window.

You should now be back at the Workbook Connection window. The data connection you just created should be listed:

Importing the Table Data

You can close the Workbook Connection window. We need to click on theExisting Connectionsbutton in the Data ribbon of Excel. The Existing Connections button should be located to the left on the Data ribbon.

Clicking on theExisting Connectionsbutton should present you with the Existing Connections window. You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:

Make sure the data connection you created in the previous steps is highlighted and then click on theOpenbutton. You should now see theImport Datawindow:

For the purposes of this post, we are going to use the default settings on the Import Data window. Next, click on theOKbutton. If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.

For this post, the table we were working with had two fields. The first field is an auto-increment INT field titled ID. The second field is VARCHAR(50) and is titled fname. Our final spreadsheet looks likes like this:

As you’ve probably noticed, the first row contains the table column names. You can also use the drop down arrows next to the column names to sort the columns.

Wrap-Up

In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications.Read Aseem’s Full Bio

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

Comment*

Name*

Email*

Website

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Connecting Excel to MySQL#

Preparation#

Creating the DSN#

Creating the Spreadsheet Connection#

Importing the Table Data#

Wrap-Up#

Leave a Reply#