Excel connects to MySQL, imports Excel data into Mysql,mysql for Excel,

Excel connects to MySQL

Even today we have powerful reporting tools like Microsoft Crystal Reports and other flexible customer management applications, and Microsoft Excel is still the most commonly used tool when analyzing sales statistics and revenue information. This is certainly not for no reason: Excel has become an indispensable tool in the office environment with its powerful and rich variety of functions.
Now, however, companies are gradually starting to store data in remote databases so that employees can read and modify data from different locations. However, the previously inherent workflow habits are difficult to break. What do you do when your boss needs to take a look at the latest sales data from the far end using Excel in the pie chart situation?
Maybe you didn't think that you could connect Excel to a database and not just Microsoft SQL Server. In fact, Excel can connect to all major 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.

installation MyODBC
ODBC provides a standard way to perform almost any common task, 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, 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, and if you are a broadband connection, the download should be completed very quickly.
Once you have finished downloading MYODBC, click on the icon to start the installation. If you do not have special installation requirements, choose a typical installation. Click the Install button to start the installation, after the installation is complete click the Finish button to launch the installation program.

Configuration MyODBC
If your operating system is a Windows data source (ODBC). à management tool à control Panel à set ÀXP, you can open the ODBC terminal as follows: Start menu

Figure 1 , ODBC Data Source Manager

Click the "Add" button to add a new data source and scroll through the sliders until you find the MySQL ODBC driver. Double-click the item to begin the configuration process. The configuration window appears as shown in.

Figure 2 , Increase MySQL Data Source

After filling in the options, click the "Test" button to confirm that the driver is connected to MySQL. If your database server is on the same machine as Excel, "localhost" or "127.0.0.1" is used as the server address, it may not take effect. Then, you can use a point as the server address so that the driver uses the named channel instead of TCP/IP to connect to the database. If you're connecting to a remote database, don't forget to make sure the firewall is blocking the port you're connected to MySQL (usually 3306). Finally, don't forget to use an existing legitimate login account. If you need to do more than just read the data, but also need to insert and update the data, then pay attention to the login account to have the appropriate permissions.
Once the test connection is successful, click on the "OK" button and the list of existing data sources will be one more. Next, we'll connect Excel to MySQL.

Connection Excel to the MySQL
To do this exercise, we will connect Excel to a MySQL table called contacts, which contains contact information for different partners and related individuals across the country. The MySQL table structure is as follows:

Create TABLE Contacts (rowid smallint unsigned not NULL auto_increment, FirstName varchar() NOT NULL, LastName varchar(+) NOT NULL, email varchar(not NULL), Title varchar() NOT NULL, Company varchar(+) NOT NULL, Phone Char(ten) is not NULL, Revenue Decimal(5,2) NOT NULL, Primary KEY (ROWID));

I have populated some sample data in these tables and you will soon see them in Excel. To manage this data from Excel, do this:

1. Open Excel, go to data on the menu, and select Import external data to import the data.
2. From the window that appears, 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. From the window titled "Connect to an ODBC data source", select the MySQL ODBC data source you just created.
5. From the window titled "Select Database and Table", select the appropriate database and table you want to connect to, 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 this data, you can select an existing worksheet or specify to create a new worksheet. Click the Confirm (OK) button after selecting.

Figure 3 , connecting to a specified database and table

If all goes well, the information you need will be shown in Excel, 4.

Figure 4 , in Excel View in Contacts Database

In this way, users can start to create a variety of charts freely. For example, creating a pie chart is a piece of cake, as shown in 5.

Figure 5 , each contact's income pie chart

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.
MySQL's Myodbc drive opens up a door for customers to connect databases to different client applications. I hope this essay will make you extrapolate and achieve similar operations.

import Excel data to MySQL

1. Copy the selected data to a txt text file (remember to remove the trailing space). ), if it is stored in the "D:\data.txt" position.

2. Build the MySQL database and table based on the data you want to import, and then go to the command prompt using the command

Load data local infile ' D:\data.txt ' into table exceltomysql fields terminated by ' \ t ';

To import an operation

Specific or reference to master:

Http://hi.baidu.com/harite/blog/item/6a5fb9de55e6f258ccbf1adf.html

Thank you very much, I also make a note

Manual Excel data and MySQL data conversion

Today is the national mathematical modelling competition, the classmate selected a topic needs to a large Excel table to carry on the statistic, the good friend son ~ ~ then helped him to complete the data from Excel to the MySQL transformation. Write down the specific steps to share to everyone, also lest everyone to the Internet everywhere search.

If you want to import the Excel table into the MySQL database,

，

Step One:

Select the data you want to import, plus one more column, such as:

Step Two:

Copy the selected data to a new table workbook and then "Save as"-"Text File (tab split) (*.txt)" If it is stored in the "D:\data.txt" position.

Step Three:

Create a MySQL database and table based on the data you want to import, and then use the command

Load data local infile ' D:\data.txt ' into table exceltomysql fields terminated by ' \ t ';

The import operation. Such as:

Now that the data has all been imported into MySQL,

Let's select a little bit.

To this, the data from Excel to MySQL conversion has been completed.

Next, the data from MySQL to Excel conversion, the process is actually the use of the "tab split" text file.

You can do this if you want to import the boys ' information from this table into Excel.

SELECT * into outfile ' D:\man.txt ' from exceltomysql where xingbie= "male";

In this way, all the boys ' information in the table is output to the text file D:\man.txt file, which ends with a tab segment, ' \ n '.

You can open Excel and choose Import data, import external data, and select D:\man.txt to make sure it's all right.

Finally, I wish you good luck ~~yours [email protected]

 for Excel is a tool included in MySQL installer that allows developers to directly manipulate MySQL databases via Microsoft Excel, such as:    export MySQL data to Excel    Import Excel data into an existing MySQL data table or create a new datasheet    directly in Excel to edit MySQL data 1.1 for 1.1. 1 GA has been released download: http: //

Lazy Lazy Way: Import Excel data into MySQL don't talk to me about ODBC import, write a small script to import or use other software, although flexible but sometimes it is really frustrating people. Open Excel, work with the columns you need, and delete the columns you don't need. Save as a CSV file. Special NOTE: If your MySQL default encoding is UTF-8 You need to save the CSV file as UTF-8 encoding format with EditPlus and other software, because the Excel default encoding format is ansi,mysql when importing Chinese data, it is likely to warning:1366Incorrectstringvalue is then built into MySQL, and the order of the fields is exactly the same as in Excel. (Do not build any other columns, including IDs) and then run the following statement: Load data local infile'[your CSV file path]'into table [table name] fields terminated by','; For example: Load data local infile'E:\\dev\\a.csv'into table T1 fields terminated by','Note that the CSV file delimiter is a comma, so the last terminated by followed by a comma. If it is a tab, use for example: Load data local infile'E:\\dev\\a.csv'into table T1 fields terminated by'\ t'This step, remember to check log, must not be warning to be successful. Finally alter Table, join the ID column (primary key,notNULL, auto increment) and other columns.

Http://wenku.baidu.com/view/ece4ace06137ee06eef91814.html

Http://wenku.baidu.com/view/2cb3c0d87f1922791688e8af.html

Http://wenku.baidu.com/view/b6cf173167ec102de2bd8992.html

MySQL export Excel file Several methods method a PHP tutorial with mysql command and ShellSelect* Into outfile'./bestlovesky.xls'  fromBestloveskywhere 1ORDER BY id DESC limit0, -method Two Bestlovesky.xls open in text mode, then save As, select ANSI encoding in encoding, save Echo"Select Id,name from Bestlovesky where 1 order by id DESC limit 0,"| /usr/local/mysql/bin/mysql-h127.0.0.1-uroot-p123456 >/data/Bestlovesky.xls method three MySQL your_database-uroot-p-E"SELECT * from Test.table2">/home/Test.xls Download the file locally with the SZ command, open if the language is garbled, because office defaults to gb2312 encoding, the server-side generation is probably UTF-8 Code, this time you have two options,1. Use iconv on the server side to encode the conversion iconv-futf8-tgb2312-Otest2.xls Test.xls If the conversion goes well, it can be downloaded from the server. If the conversion is not successful, you will be prompted: Iconv:illegal input sequence at position1841similar to this error, first download the Test.xls, this time the file is UTF-8 encoded, opened with Excel, garbled. Open Test.xls as text, then save As, select ANSI encoding in encoding, save. 

Excel connects to MySQL, imports Excel data into Mysql,mysql for Excel,