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…
Step One
Download and install MyODBC-3.51.11-2-win.msi
Step Two
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.
Step Three
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:
November 2nd, 2005 at 2:45 am
Is it possible to edit data this way?
November 2nd, 2005 at 5:28 am
This is great. I’m wondering, however, what the benefit is of connecting directly using Excel, when there are many powerful database-manipulating apps available? I’m not necessarily a sceptic, just looking for a good reason to do this.
(Nice work, by the way.)
November 2nd, 2005 at 11:56 am
Tins: I believe you can update the mysql tables with excel, though I haven’t tried it yet. I’ve got a couple projects to knock out first but that’s is for sure on the list..
Jay: Personally I want to pull data out of a MySql database into excel to analyze it. For instance I can grab my stats table with Excel and graph/manipulate the data as I please or possibly use the example given, my posts table, to examine my posting habits over time, etc,etc.. I can manipulate data with excel easier than with most programs.
Exploring the excel/mysql combo for using Excel as my presentation layer and mysql as a backend may also be an alternative for those using a Excel/WinSQL or Excel/Oracle combo, because MySQL is free.
November 2nd, 2005 at 5:22 pm
Cool. Thanks for sharing. Could Excel dynamically update values as MySQL data change? That’d be even better.
November 2nd, 2005 at 7:38 pm
I’ve written a companion guide that builds on Mark’s work. It shows how to use the same ODBC driver to connect to a MySQL database, but in OpenOffice Calc 2.0, rather than Excel. Cheers!
November 3rd, 2005 at 4:04 am
Awesome jeff, a truly free solution: 00/mysql
November 3rd, 2005 at 2:22 pm
You can do it with mydb studio with one click ;-), futhermore te software is free…
November 3rd, 2005 at 2:23 pm
http://www.mydb-studio.com
September 18th, 2008 at 6:44 pm
Great ! Génial ! Thanks ! Merci ! you save me !
September 18th, 2008 at 6:45 pm
..but exel cannot modified db, it must done by another app …
October 28th, 2008 at 10:34 pm
is it possible to use that via vba? i.e. connect via vba to the mysql db to run queries from excel? for example, like this here for access
http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_in_Microsoft_Excel/427.html
just for myql?