Excel is still the most popular product when it comes to working with spreadsheets or analysing data. I often analyse large spreadsheets with data I got from a database.
- We will need a driver:
MySQL provides a lot of connector files on their website, it’s pretty easy to navigate and find what you are looking for, but, just in case, here is the link to the ODBC files I will be using for an Windows XP conncetion: http://dev.mysql.com/downloads/connector/odbc/#downloads
- We will need the data source as a ODBC connection in Windoze:
Go Start > Control Panel > Administrative Tools > Data Sources (ODBC) > Add…
and find the newly installed MySQL driver. Then it will ask you to enter your details and you should be half way there already…
- It’s already time to use Excel:
so start Excel… There are now different ways of getting data into Excel. I want to use my own query, so I’m going to use the ‘From Microsoft Query’, as it recognises the existing ODBC connection we established.
Then I’m going to connect to the ‘localhost’ connection I previously created:
To get further, I need to select at least one field from the database. It doesn’t matter if it’s a relevant field, because a query tool will appear at the end of the dialogue.
It comes in form of a question, and you select the lower radio button:
Finally, you can add your own query (SQL button).
And the result: