Using MySQL data in Excel

You can do more than just read (SELECT) data using Excel in conjunction with MySQL.  Here’s how.

First of course, you have to install the mysqlODBC 5.1 driver.  Then you use Control Panel | Administrative tools | Data Sources to create the data source for your data. This step is where you put in how to connect to the database and what the account (root) and password (”) should be, and if you want which database to access. It should look like this:

ODBC Data source Administrator Popup

Select ADD a source. On the new popup, scroll down to MySQL ODBC Driver 5.1. Should look like this:

Create New Data Source

Select MySQLODBC 5.1Driver, and click Finish.  You should get a new window that actually comes from your properly installed ODBC Driver itself. Note the logo!

ODBC Data source window

Use localhost for the name of the host.  You can put any user that has access to the data, but root has access to everything. When filled in it should look something like this:

Filled in Data Source Form

Now start Excel. Get a blank worksheet (not essential– you can actually load data anywhere into a sheet; Excel will ask you where to put it, and will warn you before if you accicentally are going to overwrite some other data).

Go to Data | Import External Data | Import Data.

Import Data from external source

You should get a popup window that asks you to choose a data source. Select the New Source button.

Now you will get a data connection wizard window, which allows you to create a data connection for the table you want.  Select ODBC DSN.

Select the data source you created earlier.

Select My Data Source

Select the database (and table if you wish) you want on the next popup menu.

You will now get a popup asking you which .odc file you want– pick the one you just created! Then click OPEN button.

If you have not selected a table earlier, you will get a popup to ask you to choose a table, like this one. Pick the table you want.

Now you will see the Import Data popup, which will ask you where to put the data. Choose a cell for the upper left hand corner of the data. Or choose a new sheet for putting it there.  Click OK to do this.

You should shortly see the data appear in your sheet.

Imported Student Data in Excel