Tag: excel

Connecting your MySQL data with MS Excel

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. 

While it’s not too much effort to copy the data into the spreadsheet when you do need something ad hoc, it can be quite frustrating if you are preparing the same reports over and over again. It would be much easier to just integrate the query as a data source in Excel.
Excel has already got great features to tie into MSSQL, probably because they want to sell more of their own stuff, but I suppose it should be just as easy connecting it with MySQL…
  1. 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
  2. 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…
  3. 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:

It will take a while when you enter your own query, very much depended on how complex your query is, but I hope for you it takes under five minutes to run 😀
Let me know if that works for you or if you have any breaking points.