Connect Excel to Mysql Database
The following will connect Excel to a Mysql database, then import a table into Excel.
This was tested on an Excel 2003 machine connecting to MySQL 4.1.14 (this website’s database).
After reading numerous tutorials on how to connect Excel to Mysql they were all saying the same thing, and all of them failed (for me). This was because they were never specific on the driver to use. After reading this quick note and modifying some urls to find the download location, I achieved a successful connection, and here’s how…
Download and install MyODBC-3.51.11-2-win.msi
Add a new datasource to your windows environment. From your start menu click Settings->Control Panel.
In the newly opened folder click Administrative Tools->Data Sources(ODBC).
Click the Add Button, scroll down the list and double click the MySql 3.51 driver. Enter a name for the connection and your database info then hit test. With some luck you will get a Success message. Your data connection is now set up.
Back in Excel: On the main menu click Data->Import External Data->Import Data.
In the newly opened dialog box click the New Source button towards the bottom then double click ODBC DSN. You should now see the datasource you setup in the prior section. Double click this.
Select the table you want from the query editor, hit Next->Finish.
Almost finished, you should be brought back to the “Select Datasource” dialog again, click Open now and you will be asked what cell to put your new table data in. Pick your cell and click OK. You will be asked to review your database settings:
Note 1: Triple check your credentials, for some reason my username is truncated here.
Note 2: This is the step that fails by picking a random driver from the MySql site (even though test connection had worked).
Cross your fingers, click OK, and bammo your Mysql dataset is now in Excel. Here’s a snippet of my posts table in Excel: