Use Excel to analyze MySQL Data

Guidance:This article mainly describes how to useExcelToMySQL DATAThe following is a description of the specific solution. Currently, many companies use MySQL DATA to store remote databases. This allows employees to read and modify data from different places. I believe that through the study in this article, everyone has gained some benefits.

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.

Installing mydbcodbc provides a standard way to execute almost all common tasks, such as MySQL **, 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 as follows: Start Menu: configure the MySQL Data Source (ODBC) in the logging control panel using the logging management tool ).

Click "Add (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 connecting you 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 in the existing MySQL Data Source list. Next, we will connect Excel to MySQL.

At this point, the analysis of MySQL Data Using Excel is basically complete, and you can proceed with the future work. I hope the content mentioned above will be helpful to everyone.

Edit recommendations