connecting excel to mysql

7/28/2019 Connecting Excel to Mysql

1/15

CONNECTING EXCEL TO MySQL

Sure Excel is used for spreadsheets, but did you know you can connect Excel to external data

sources?

In this article were 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

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

Make sure after you download the file that you check the files 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 XP system, you will need to click on Start, then Control Panel,

then Administrative Tools, then Data Sources (ODBC). You should see the following

information:
http://dev.mysql.com/downloads/connector/odbc/http://dev.mysql.com/downloads/connector/odbc/


2/15

Notice the tabs in the image above. A User DSN is only available to the user that created it. A

System DSN is available to anyone that can log into the machine. A File DSN is 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 the Add button near the top right corner. You should be

presented with a window that looks something like this image:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/ODBC_data_source_admin.png


3/15

You will probably have to scroll down to see the MySQL ODBC 5.1 Driver. If its 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.1 Driver is highlighted and click on

the Finish button. You should now see a window similar to the one listed below:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/create_new_data_source.png


4/15

Next you will need to supply the information necessary to complete the form shown above. The

MySQL database and table were 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

the Test button to make sure everything is in working order. Next, click on the OKbutton.

After you have click on the OK button, 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. Itshould look something like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/data_source_config.png


5/15

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 the Data ribbon.

You should see something like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/dataribbon.pnghttp://www.online-tech-tips.com/wp-content/uploads/2010/01/ODBC_data_source_after.png


6/15

The next step is to click on the Connections link 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. It should look something like this:

The next step is to click on the Add button. This will present you with the Existing Connections

window and it should look something like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/workbook_conn.png


7/15

Obviously you dont want to work on any of the connections listed. Therefore, click on

the Browse for More button. This will present you with the select data source window and it

should look like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/existing_conn.png


8/15

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.odc folder. In doing so, you should be now see the Data Connection

Wizard window. It should look something like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/select_data_source.png


9/15

Given the data source choices listed, you want to highlight ODBC DSN and 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 on Next.
http://www.online-tech-tips.com/wp-content/uploads/2010/01/select_data_source_2.png


10/15

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 the Finish button in the lower right

of the window.


11/15

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

created should be listed. It should look something like this:


12/15

Importing the Table Data

You can close the Workbook Connection window. We need to click on the Existing

Connections button in the Data ribbon of Excel. The Existing Connections button should be

located to the left on the Data ribbon. See image below for approximate location:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/existing_conn_1.pnghttp://www.online-tech-tips.com/wp-content/uploads/2010/01/select_data_source_5.png


13/15

Clicking on the Existing Connections button should present you with the Existing Connections

window. Youve seen this window in previous steps, the different now is that your data

connection should be listed near the top. The Existing Connections window should look

something like this:

Make sure the data connection you created in the previous steps is highlighted and then click on

the Openbutton. You should now see the Import Data window. Hopefully, it looks something

like this:
http://www.online-tech-tips.com/wp-content/uploads/2010/01/existing_conn_2.png


14/15

For the purposes of this post, we are going to use the default settings on the Import Data

window. Next, click on the OKbutton. 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

incrementing INT field titled ID. The second field is VARCHAR(50) and is titled fname. Our

final spreadsheet looks likes like this:

As youve 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.
http://www.online-tech-tips.com/wp-content/uploads/2010/01/final.pnghttp://www.online-tech-tips.com/wp-content/uploads/2010/01/import_data.png


15/15

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.

This process isnt going to be used as the main stream means of gathering MySQL data. It could

prevent the daily/weekly/monthly call where someone requests a few pieces of basic data from a

table and they want it in spreadsheet form.

Use the tricks from this post to give the user the ability to retrieve the data themselves from a

database table view. For those of you looking for something a little more advanced, stay tuned

for a future post regarding the Microsoft Query tool that you can also use with Excel andexternal data sources.

* Apple users: If you are running an Intel powered system, please see

http://forums.mysql.com/read.php?37,194261,194261#msg-194261

For more information. At the time of this post, this issue is still unresolved. Tested on a 10.5.8

system with Office 2008 for Mac and the same issue was found as the post on the MySQL

forum.
http://forums.mysql.com/read.php?37,194261,194261#msg-194261http://forums.mysql.com/read.php?37,194261,194261#msg-194261

connecting excel to mysql

Documents