So, a continuation from this thread (which covered how to create a MySQL tick database):
Now we want to query some of this data from Excel.
I am using Windows 7 x64, and Office 2010. The instructions below should be somewhat universal.
Step 1: Download the ODBC driver for Windows. ODBC is "Open Database Connectivity" and is basically the driver that lets many Windows apps talk to our MySQL server. v5.1.11 is the latest as of this post.
MySQL :: Download Connector/ODBC (
Step 2: Create the DSN and setup credentials. DSN is "Database Source Name". Control Panel -> Administrative Tools -> ODBC sources.
A "user" DSN means it is available to your logged in user. "System" DSN means any user on the system. User should suffice for 99% of you guys, and is what I am using.
Click "add" and choose MySQL ODBC driver.
You can enter whatever you want for the Data Source Name and Description. For the TCP/IP server, enter the remote IP address of your MySQL server if it is not installed locally on your machine. (In my example, it is on my LAN at 192.168.1.11). If your server is on the same physical machine, just type in localhost or 127.0.0.1.
Enter the user and password. These are your MySQL credentials, which you should have already created when you created and populated the MySQL database.
If the credentials and IP are correct, you can click the drop down arrow and see a populated list of available databases to choose from. I am selecting "ticks", as that is the name of my db.
Now we have the entry we just created:
Step 3: Load up Excel and connect to the MySQL db via the ODBC connection.
Click on "Data" in the ribbon interface, then "Connections". This will open a new window:
Click Add:
Then "Browse for More":
Then "New Source":
Choose "ODBC DSN":
Then choose your DSN. I named mine "excelmysql".
You should now see the tables on your database. I have just one table on this database, called "qcollector":
Now we can save this new connection:
Now our workbook connection is shown:
Step 4: Let's import some data from MySQL to Excel.
Data -> Existing Connections:
Accepting the defaults:
I have a huge database, so I hit "Properties" and modified the query by adding "LIMIT 100" to the end, so that I only get 100 results.
You can further modify the query to get the exact results you desire.
And we're done! Now you have data from MySQL!
You can also set the query to auto-refresh every 1 minute, or etc, if that is useful to you.