Using Excel to analyze MySQL data

Even though we have powerful reporting tools like the Microsoft Crystal Report and other flexible customer management tools in our current era, Microsoft Excel is still the most common tool when analyzing sales statistics and revenue information. This is certainly not without reason: Excel, with its powerful and rich variety of functions, has become an indispensable tool in the office environment.

Now, however, companies are gradually storing data in remote databases, which allows enterprise employees to read and modify data from different places. However, the previously inherent workflow habits are difficult to break. What do you do when your boss needs to look at the latest sales figures from the remote use of Excel in the pie chart situation?

Perhaps you didn't realize that you could connect Excel to a database and not just Microsoft SQL Server. In fact, Excel can connect to all the mainstream databases, such as MySQL, PostgreSQL, and Oracle, as long as the database provides ODBC drivers. In this article, we'll take MySQL as an example to learn how to connect Excel to MySQL.

Install MYODBC

ODBC provides a standard way to perform almost all common tasks, such as data recovery, deletion, and selection, which means that for different databases, different drivers are written to interpret the user's commands as standard ODBC standards. MySQL already has a specific ODBC driver available, which is known as MYODBC. On every computer you want to use Excel to connect to MySQL, you need to install the MYODBC driver.

Now, first download the MYODBC installer (HTTP://DEV.MYSQL.COM/DOWNLOADS/CONNECTOR/ODBC). The installation package is only 2.3M, if you are a broadband connection, the download should be completed soon.

Once you have finished downloading the MYODBC, click the icon to start the installation. If you do not have special installation requirements, choose a typical installation. Click on the Installation button to start the installation, installation completed after the click on the completion button to launch the installer.

Configure MYODBC

If your operating system is Windows XP, you can open the ODBC terminal as follows: Start menu-Settings-Control Panel-admin tools-Data Source (ODBC).

Figure 1, ODBC data Source Manager

Click the Add button to add a new data source and scroll through the slider until you find the MySQL ODBC driver. Double-click the item to begin the configuration process. The configuration window is shown in the following figure.

Figure 2, increasing the MySQL data source