Connect Excel to Mysql Database

November 1st, 2005 | Categories: Data | Tags: , , ,

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


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. November 2nd, 2005 at 02:45
    Reply | Quote | #1

    Is it possible to edit data this way?

  2. November 2nd, 2005 at 05:28
    Reply | Quote | #2

    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
    November 2nd, 2005 at 11:56
    Reply | Quote | #3

    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. November 2nd, 2005 at 17:22
    Reply | Quote | #4

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

  5. November 2nd, 2005 at 19:38
    Reply | Quote | #5

    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
    November 3rd, 2005 at 04:04
    Reply | Quote | #6

    Awesome jeff, a truly free solution: 00/mysql

  7. elli
    November 3rd, 2005 at 14:22
    Reply | Quote | #7

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

  8. elli
    November 3rd, 2005 at 14:23
    Reply | Quote | #8
  9. Kmmp
    September 18th, 2008 at 18:44
    Reply | Quote | #9

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

  10. Kmmp
    September 18th, 2008 at 18:45

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

  11. michael
    October 28th, 2008 at 22:34

    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?

  12. anwar Husain
    February 27th, 2009 at 15:14

    Hi, is it possible to feed mysql table from excel or access or link mysql to excel?

  13. March 24th, 2009 at 23:02

    elli, why post misleading info?

    mydb studio is NOT free!

  14. iwannatoscript
    April 30th, 2009 at 18:50

    real time data to mysql to excel is iy possible?

  15. vj
    May 29th, 2009 at 07:11

    i did all the steps said above. however the MS query is not able to pick up the data tables. The error reads thus: “The specified table can’t be found, is locked by another user or by another query you’re running, or contains no valid fields.”

    Any solutions? Have been hunting around the net for quite long.

    BTW the same procedure can also be used to get data into pivots.

  16. May 30th, 2009 at 02:17

    Hi

    Thanks for the good info.

    A free Excel add-in that some people should find very useful for this type of thing is SQL Drill (http://www.sqldrill.com)

    It works fine with MySQL using the connector as mentioned above.

    It is a work in progress and any feedback is great. Thank you.
    Al

  17. June 2nd, 2009 at 14:05

    Have you created a table in the database? Sometimes it’s somthing stupid like this.

    Cheers

  18. Hozefa
    June 24th, 2009 at 23:45

    Hi,

    The link in step does not have the driver.

    Is there a link where i can get the driver.

    Cheers

  19. y0y1
    June 29th, 2009 at 08:15

    Thanks a million, it works :-)

  20. y0y1
    June 29th, 2009 at 08:22

    btw: The file can be found now here:

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

  21. Vishal
    January 4th, 2010 at 07:03

    Excellent Post. Thanks a million ….. works like a charm ….

  22. April 15th, 2010 at 05:21

    This is simply superb!. Thanks for taking your time and posting it here. Really a great thing.

  23. April 15th, 2010 at 05:23

    Also please let me know if there is way where we can update mysql database using the excel

  24. Mina
    April 19th, 2010 at 06:32

    i wonder how you can reference an excel cell from the SQL query, so if i want to make a cell dynamically change the query where condition.

    • Nate
      August 13th, 2010 at 19:29

      try this code

      Sub Macro1()

      Range(“A1″).Select
      With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
      “ODBC;DSN=SQL to Excel;”, Destination:=Range(“$A$1″)).QueryTable
      .CommandText = Array(“SQL Query”)

      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = “Table_Query_from_SQL_to_Excel”
      .Refresh BackgroundQuery:=True
      End With

      End Sub

  25. June 21st, 2010 at 12:06

    This is great.
    My friend wants a system to manage stock levels of products and wants to be able to track it remotely, online via the internet and locally within his store, offline (where there is currently no internet access).

  26. CB
    June 28th, 2010 at 22:11

    Is there a way to run a mysql query first before the data gets imported to excel?

  27. jaydee
    August 5th, 2010 at 09:37

    Hi, great page Mark.

    Anyway, I had some problems as those succinctly mentioned in Note 2 of step three. In my case, the connection test was successful in the controlpanel/admtools/datasources(ODBC) window, but unsuccessful in the excel import data/… one.

    My problem was on the MySQL’s ODBC connector. I was using a x32 in a x64 windows. When I changed for the x64 one, I could connect from everywhere.

    I hope this helps someone.

    • William
      October 23rd, 2011 at 21:56

      I thought this was my problem too – so I installed the x64 … same issue still. In Excel it refuses the credentials.

  28. August 6th, 2010 at 04:53

    Very useful, it saved me a lot of work.

  29. Nate
    August 13th, 2010 at 15:16

    I’m with Mina. How do you have excel run a query or import an already executed query?

  30. Nate
    August 13th, 2010 at 16:02

    Ok so playing with this a little more you can run a query by clicking on the SQL button. however it will not let me do temporary tables. Anyone know how i can do that?

  31. January 23rd, 2011 at 23:24

    Thanks for the help setting up the MySQL connection inside of Excel. You can actually manipulate data on the mysql server from inside of excel using the recordsets. I have been working with accessing data in an access database for a while now and it works great, it’s just code heavy. I find that using excel as a VB editor and using the VBA forms and classes is a way of creating applications using excel as a base. To make it work with the MySQL database, I took what I had done with the Access database and just modified the connection string and it pretty much works the same.

  32. Specky
    January 29th, 2011 at 12:17

    Should the ODBC connector installer register the DLL? It doesn’t seem to, and when I manually try to register it with regsvr32 I get an error (Enry point DLLRegisterServer not found).

    Any suggestions?

  33. Specky
    January 29th, 2011 at 14:09

    Ah! Fixed. Running Win7. You need to install the 64 bit connector otherwise it doesn’t show up in the list, even though I only have the 32 bit MySQL installation

  34. Balachandar
    October 4th, 2011 at 08:32

    I have database in excel. After enter the link in text box and then click ok button i want to update that database in mysql using php.

  35. Balachandar
    October 4th, 2011 at 08:34

    I have database in excel. After i browse the link in text box and then click ok button. I want to update that database in mysql using php.

  36. William
    October 23rd, 2011 at 21:54

    Not working for me. Step one and step two worked great. But back in excel, although I could select the ODBC DNS option, and then MySQL that I named, it asked me for Data Link Properties in Excel 2007, and again for the credentials, but in this case it kept telling me the credentials were wrong. I cannot get further.

  37. 1 trackbacks