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…
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:
Is it possible to edit data this way?
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.)
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.
Cool. Thanks for sharing. Could Excel dynamically update values as MySQL data change? That’d be even better.
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!
Awesome jeff, a truly free solution: 00/mysql
You can do it with mydb studio with one click
, futhermore te software is free…
http://www.mydb-studio.com
Great ! Génial ! Thanks ! Merci ! you save me !
..but exel cannot modified db, it must done by another app …
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?
Hi, is it possible to feed mysql table from excel or access or link mysql to excel?
elli, why post misleading info?
mydb studio is NOT free!
real time data to mysql to excel is iy possible?
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.
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
Have you created a table in the database? Sometimes it’s somthing stupid like this.
Cheers
Hi,
The link in step does not have the driver.
Is there a link where i can get the driver.
Cheers
Thanks a million, it works
btw: The file can be found now here:
http://dev.mysql.com/downloads/connector/
Excellent Post. Thanks a million ….. works like a charm ….
This is simply superb!. Thanks for taking your time and posting it here. Really a great thing.
Also please let me know if there is way where we can update mysql database using the excel
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.
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
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).
Is there a way to run a mysql query first before the data gets imported to excel?
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.
I thought this was my problem too – so I installed the x64 … same issue still. In Excel it refuses the credentials.
Very useful, it saved me a lot of work.
I’m with Mina. How do you have excel run a query or import an already executed query?
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?
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.
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?
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
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.
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.
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.