Fetch data from an MySQL database trough excel

Mostly a large collection of data is stored in a relational database. Relational databases have the advantage that they are offering a high level of perfomance especially for a large number of data records and especially when having aggregated queries. SQL is the most common computer language to communicate and control a relational database management system (RDMS). It offers various possibilites to fetch data (e.g. a JOIN tables within one query in order to receive aggregated data from different tables in one result set). Furthermore it allows to make certain limitations (e.g. WHERE-statement and HAVING statement) as well as to group data (GROUP BY) and sorting. Microsoft Excel is a well known spreadsheet software which offers a lot of powerful functions for data preparation and evaluation. Excel allows you to visualize data (e.g. Pivot table or various kind of charts) and it offers a lot of formulars for calculation, aggregation and reporting. As mentioned at the beginning, data is mostly stored in databases for performance reasons but also due to the fact that a database represents a centralized storage possibility for data which can be accessed by everyone (according to the access policy of course). Webshops, ERP Systems, CMS systems, just to name but a few, are all using relational databases in most cases. MySQL is a very popular and often used RDMS but it offers not that powerful possibilites of data evaluation and reporting than Excel. So why do not user the actual data from MySQL (as the most reliable datasource), fetch them by using SQL (because of its powerful possibilities of JOINING) and process the final result set in Excel

Taggings:

Submitted by Gregor Kastner on Wed, 11/28/2012 - 22:07
1 answer

Adding a ODBC connection to MySQL and use it within Microsoft Excel

Microsoft Excel offers to connect to all common database management systems (DMBS) trough the ODBC interface. Excel has a built connector for Microsoft Access and Microsoft SQL. Since we are using MySQL we need acces the DBMS go trough ODBC.

An ODBC connection brings two components into play:

  1. ODBC driver manager

    The ODBC driver manager handels all the installed ODBC connectors. Microsoft Excel uses the ODBC driver manager which interfaces the ODBC connector to Microsoft Excel. Windows offers an integrated driver manager. To open it click the Start button -> Control panel -> Administrative tools -> Datasources (ODBC).
    ATTENTION: Since Mac OS X (10.6) there is no built in ODBC driver manager availble. Therefore it has to be manually installed under Mac OS X greater or equal than version 10.6. A recommendation is to use iODBC, an open ODBC driver manager. It is available for Linux as well. Download it at: http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads and install it

  2. ODBC database connector/driver

    This connector is needed for each particular database management system (DMBS) as its implementd DMBS specific that means there is no common connector for all DBMS available. In our case as we want to use MySQL we need the MySQL ODBC driver either for Windows or Mac OS X. Download the connector at: http://www.mysql.com/downloads/connector/odbc/
    ATTENTION: Check for which if your Excel is a 32 bit version or a 64 bit version. Even you are using a 64 bit version of Windows, if you have a 32 bit version of Excel running on it you need the 32 bit version!

Once the driver is installed, it can be configured trough the driver manager. Open either iODBC (in case of Mac OS X) or the built in driver manager of Windows (Start button -> Control panel -> Administrative tools -> Datasources (ODBC)) and you are able to add a new user data source (a so called DSN). For this DSN you must provide all relevant information (e.g server address and port, username, password, database name). A easy to understand wizard will guide you trough this process. This works quite similar under Mac or Windows.

Once the DSN is configured properly it is possible to use this database within Excel.
Open a new spreadsheet and klick on Data -> Other sources -> From Microsoft Query (see an attached screenshot of a German Excel version). Then you can select the previously configured DSN of your datasource. Select them and Microsoft SQL appears (see attached screenshot). You can enter your query here and execute it. Once the query is valid, Excel offers you different choices what to do with the retrieved data (please see an attached screenshot). You can simply add the result set to a table or transform it into a pivot table.

Submitted by Gregor Kastner on Wed, 11/28/2012 - 22:50

Taggings: