How to use Excel to analyze MySQL Data

The following article mainly describes how to use Excel to analyze MySQL Data. The following is a description of the specific solution. I hope it will be helpful for your future study. Currently, many companies use MySQL DATA to store remote databases so that employees can read and modify data from different places.

However, the previous work flow habits are hard to break. What should you do when your boss needs to use Excel at the far end to view the recent sales of MySQL Data in a pie chart? In this article, we will take MySQL as an example to learn how to connect Excel to MySQL.

Even though we have powerful reporting tools like Microsoft Crystal Reports and some other flexible customer management application tools in the current era, many enterprises analyze such sales statistics and revenue information, microsoft Excel is still the most commonly used tool. Of course, this is not without reason: Excel has become an indispensable tool in the office environment with its rich and powerful functions.

However, the company is gradually storing data in remote databases, so that employees can read and modify data from different places. However, the previous work flow habits are hard to break. What should you do when your boss needs to use Excel at the far end to view recent sales data in a pie chart?

Maybe you didn't think that you could connect Excel to a database, and not only limited to Microsoft's SQL Server. In fact, Excel can be connected to all mainstream databases, such as MySQL, PostgreSQL, and Oracle, as long as the database provides an ODBC driver. In this article, we will take MySQL as an example to learn how to connect Excel to MySQL.

Install MyODBC

ODBC provides a standard way to execute almost all common tasks, such as MySQL Data Recovery, deletion, and selection, which means that for different databases, you need to write different drivers to interpret your commands as standard ODBC standards. MySQL has long been available with a specific ODBC driver, known as MyODBC. Install the MyODBC driver on every computer that you want to connect to MySQL using Excel.

Now first download the MyODBC installer http://dev.mysql.com/downloads/connector/odbc ). The installation package is only 2.3 M. If you are using a broadband connection, the download should be completed soon.

After you download MyODBC, click the icon to start installation. If you do not have special installation requirements, select a typical installation. Click the Install button to start the installation. After the installation is complete, click the complete button to launch the installation program.

Configure MyODBC

If your operating system is Windows XP, you can open the ODBC terminal in the following way: Start Menu: configure the ODBC data source using the butler control panel and administrative tool  MySQL ).

Click "Add)" to Add a new data source and scroll the slider until you find the MySQL ODBC driver. Double-click this item to start the configuration process. Shows the Configuration window.

After filling in the options, click the "Test" button to confirm whether the driver can be connected to MySQL. If your database server and Excel are on the same machine, using "localhost" or "127.0.0.1" as the server address may not take effect. Then, you can use a node as the server address, so that the driver uses a named channel instead of TCP/IP to connect to the database. If you want to connect to a remote database, do not forget to make sure that the firewall blocks the port that you connect to MySQL, usually 3306 ). Finally, do not forget to use an existing valid logon account. If you need to not only read data, but also insert and update data, you must note that the Logon account must have the relevant permissions.

Once the connection test is successful, click "OK" to add one more MySQL Data source to the existing MySQL Data Source list. Next, we will connect Excel to MySQL.