Connect Excel to Mysql Database

Automate Excel

Connect Excel to Mysql Database

mysql1The 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:

mysql1

Related posts

11 Responses

  1. Tins Says:

    Is it possible to edit data this way?

  2. Jay Jones Says:

    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.)

  3. Mark Says:

    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.

  4. Son Says:

    Cool. Thanks for sharing. Could Excel dynamically update values as MySQL data change? That’d be even better.

  5. Jeff Costa Says:

    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!

  6. Mark Says:

    Awesome jeff, a truly free solution: 00/mysql

  7. elli Says:

    You can do it with mydb studio with one click ;-), futhermore te software is free…

  8. elli Says:

    http://www.mydb-studio.com

  9. Kmmp Says:

    Great ! Génial ! Thanks ! Merci ! you save me ! :)

  10. Kmmp Says:

    ..but exel cannot modified db, it must done by another app …

  11. michael Says:

    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?

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.