Analysis of MySQL Data available in Excel

The following article describes how to use Excel to analyze MySQL Data. The following article describes how to use Excel to solve MySQL Data. If you are interested in it, you can browse the following articles and hope they will help you in your future studies.

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 a remote MySQL database, 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 MySQL database, and not only limited to Microsoft SQL server (WINDOWS powerful database platform ). In fact, Excel can be connected to all mainstream databases, such as MySQL (the best combination with PHP), PostgreSQL and Oracle (large website database platform), and so on, as long as the database provides an ODBC driver. In this article, we will take MySQL (the best combination with PHP) as an example to learn how to connect Excel to MySQL (the best combination of PHP and MySQL ).

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 MySQL databases, you need to write different drivers to interpret your commands as standard ODBC standards. MySQL (the best combination with PHP) has long been available with a specific ODBC driver, known as MyODBC. On every computer that you want to connect to MySQL (the best combination with PHP) using Excel, you need to install the MyODBC driver.

Now, first download the MyODBC installer http://dev.MySQL (the best combination with PHP). 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? Set? Control panel? Administrative Tools? Data Source ODBC ).

(Best combination with PHP) Figure 1) "width =" 461 "height =" 377 "/>

Figure 1 ODBC data source Manager

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

(Best combination with PHP) data figure 2) "width =" 509 "height =" 432 "/>

Figure 2. Add a MySQL Data Source

After filling in the options, click the "Tesct test)" button to confirm whether the driver can be connected to MySQL (the best combination with PHP. 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 (the best combination with PHP), 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 (the best combination with PHP ).

Tomcat (a good JSP running platform) 5. Configure the MySQL (the best combination with PHP) Data Source

Use PHP to convert a MySQL (best combination with PHP) data table to an excel File Format

How to set up a data source using MySQL (the best combination with PHP) in jboss4.0

Set MySQL for UNIX (the best combination with PHP) Data Synchronization for replication

Obtain MySQL Data from the Web using Access

Three methods for querying MySQL (the best combination of php and PHP) data through php access

Connect Excel to MySQL (best combination with PHP)

To do this exercise, we will connect Excel to a MySQL table named contacts (the best combination with PHP), which contains contact information of different partners and related individuals nationwide. The table structure of this MySQL (best combination with PHP) 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,
Prima (the most comprehensive VM management system) ry 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 data source you just created (the best combination with PHP.

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.

MySQL DATA figure 3) "width =" 498 "height =" 362 "/>

Figure 3. connect to a specified database and table

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

(Optimal combination with PHP) data figure 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.

(Best combination with PHP) data figure 5) "width =" 439 "height =" 320 "/>

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 MySQL (the best combination with PHP) opens the door for connecting the MySQL database to different client applications. I hope this short article will allow you to perform similar operations.