An easy way to import data from Excel to MySQL _ MySQL

A simple method to import data from Excel to MySQL. the author introduces a simple method to import data from Excel to MySQL.
This method is the simplest but not a silly one. you need to know a little about Excel and MySQL.

Of course, I provided an Excel file as an example at the end, but you still need to write the INSERT statement of MySQL to debug possible errors.

1. in the last column of the data to be imported, write an Excel formula (the cell below "SQL" in this example, "SQL" is not necessary, just for convenience ).

= CONCATENATE ("insert into employee (name, birthday, email, Hober) values ('", RC [-4], "', '", RC [-3], "','", RC [-2], "','", RC [-1], "');")

This will generate an insert statement, as you can see in the Excel file in the example (when you click the cell below SQL, you can see the formula ).

This actually uses the CONCATENATE string connection function of Excel to generate the SQL statement you need. RC [-2] is the second column from the last column (excluding this column.

2. copy the formula of cells under "SQL" to other cells in the column, so that all the SQL statements are generated.

3. select the last column, copy the data to a text file, and save the text file as a. SQL file (do not forget to delete the first line of "SQL ").

4. there are many tools that can run SQL files. you can run this file. If there is no tool, see batch execute mysql scripts (http://www.albertsong.com/read-21.html) in windows ).

Using the above method, you need to know what insert statements are correct for mysql.

The most common format is the date format. if you do not convert it to a string, an integer will be generated using the above method. This will make it wrong after mysql is inserted.

Therefore, the formula above is changed

= CONCATENATE ("insert into employee (name, birthday, email, Hober) values ('", RC [-4], "', '", TEXT (RC [-3], "yyyy-mm-dd hh: mm: ss"), "','", RC [-2], "','", RC [-1], "');")

The TEXT function is used to format a date string that mysql can accept.

For more information, see examples. Excel2Mysql.rar

File description:

Doc.txt -- this article

SqlGen.xls -- Excel example file

Create. SQL -- table creation statement

Insert. SQL -- the generated insert statement

Function.txt -- functions to be used in Excel

After writing so many statements, we can use the CONCATENATE function of Excel to generate and execute SQL statements.

If you know a little about VBA, you can directly generate the insert. SQL file.