[Mysql] imports an Excel table to one of Mysql tables, mysqlexcel

[Mysql] imports an Excel table to one of Mysql tables, mysqlexcel

Assume that the table contains three columns: A (integer field), B (integer field), and C (string data, the fields to be inserted in the table are col1, col2, col3

1. In any column, suppose in column D, input: = CONCATENATE ("insert into table (col1, col2, col3) values (", A2, ",", B2, ", '", C2, "');"), because A1, B1, and C1 generally store headers in Excel, the data starts from the second row, then, pull down the complete table like the sum and the average formula,

2. Press Ctrl + C to copy the column to notepad and paste it. A lot of insert statements are obtained.

3. If you open MySQL Command Line syntax and input an SQL statement containing Chinese characters, garbled characters will appear.

4. Enter the password, log on, and enter the "use name of the database you want to operate;" Press enter. The semicolon cannot be saved because in MySQL Command Line Client, use semicolons to distinguish between statements.

5. Enter "set names encoding of your database;", which is usually gbk or utf8 or gb2312. Note that the UTF-8 code must be expressed as utf8 here. If this sentence is absent, if you insert Chinese characters, an error is returned.

6. Copy the many insert statements in notepad, right-click and paste them in the command line, and do not use Ctrl + V. After a long mysql execution process is completed, import the Excel file to Mysql.

This method can also be used with other SQL statements and databases.

How to import data from an excel table to a mysql table

This is simple. It will be finished in one minute.
Step: Create an ACCESS database
Connect a non-partition table to the data, that is, the table to be written to MYSQL.
Then, import the data in EXCEL to ACCESS.
Use the query designer to design a query to directly write table 2 to Table 1. OK
I want to finish the whole process within 1 minute.

Mysql Query Language: SQL statement for inserting data from one table into another table

1. The table structure is identical
Insert into Table 1
Select * from table 2
2. The table structure is different (in this case, you must specify the column name)
Insert into table (column name 1, column name 2, column name 3)
Select column 1, column 2, column 3 from table 2