In MySQL, the Export and import of Excel file data

Export of data:

SELECT * FROM mytable into outfile ' D:/mytable.xls ';

You can find the Mytable.xls file under the packing directory.

Import of data:

Import with exported table Mytable.xls: Input command: Load data infile ' d:/mytable.xls ' into table mytable;

Note: mytable: is the table name of the database

When a table in the database has a primary key and a self-increment field, an error occurs, the data cannot be added, and the solution:

2. Remove the column from the Mytable.xls table and the primary key
4. Input command: Load data infile ' d:/mytable.xls ' into table mytable(name,count); The data is appended, not overwritten.
6. Self-increment field: auto_increment, primary key: Primary_key

7. LOADDATA[low_priority | CONCURRENT] [LOCAL]INFILE'file_name'    [REPLACE | IGNORE]     into TABLETbl_name[CHARACTER SET charset_name]    [{fields | COLUMNS} [TERMINATED by ' string ']        [[Optionally]Enclosed by 'Char']        [escaped by ' char ']    ]    [LINES [Starting by ' string ']        [TERMINATED by ' string ']    ]    [IGNORE Number {LINES | ROWS}]    [(Col_name_or_user_var [, Col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}]...]

In MySQL, the Export and import of Excel file data