Demonstration of connecting Excel to MySQL database

The following article describes how to connect Excel to the MySQL database. If you have a good understanding of the technology of connecting Excel to the MySQL database, in practice, you can get the result. The following describes the specific content.

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 the MySQL database as an example to learn how to connect Excel to the MySQL database.

Install MyODBC

ODBC provides a standard way to execute almost all common tasks, such as 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 a Windows Data Source ODBC ). You can open the ODBC terminal as follows if you set the management tool to control panel to XP: Start Menu

ODBC data source Manager

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.

Add a MySQL Data Source

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 the remote database, do not forget to make sure that the firewall blocks the port connecting you to the MySQL database, which is 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 data source in the existing data source list. Next, we will connect Excel to MySQL.

Connect Excel to MySQL

To do this exercise, we will connect Excel to a MySQL database table named contacts, which contains contact information of different partners and related individuals nationwide. The MySQL table structure is as follows:

 
 

  
  
create table contacts (rowid smallint unsigned not null auto_increment,  
  
  
firstname varchar(20) not null,  
  
  
lastname varchar(30) not null,  
  
  
email varchar(55) not null,  
  
  
title varchar(20) not null,  
  
  
company varchar(30) not null,  
  
  
phone char(10) not null,  
  
  
revenue decimal(5,2) not null,  
  
  
primary key(rowid));  
  
  
 
 
 

I have filled some sample data in these tables and you will soon see them in Excel. To manage the data in Excel, follow these steps:

1. Open Excel, go to "data" on the menu, and select import external data to import data.

2. In the displayed window, select "connect to new data source. odc ".

3. A Window titled "welcome to the Data Connection Wizard" appears. Select "odbc dsn" from this window ".

4. In the window titled "connect to ODBC Data Source", select the MySQL database ODBC data source you just created.

5. In the "select database and table" window, select the database and table you want to connect to, as shown in 3. Select the table and click Next.

6. In the last window titled "save data connection file and end", select the end button.

7. You will be asked where to store the data. You can select an existing worksheet or specify to create a new worksheet. Click OK.

Connect to a specified database and table

If everything goes well, the information you need will be displayed in Excel, 4.

View the contacts Database in Excel

In this way, you can freely create various charts. For example, creating a pie chart is a piece of cake, as shown in Figure 5.

Revenue pie chart of each contact

If you need data across tables, you can try to create a view in the database, and then use Excel to connect to the view.

The MyODBC driver of the MySQL database opens the door for connecting the database to different client applications. I hope this short article will allow you to perform similar operations.