Possible problems in inserting Excel data into MySQL database

1. take excel Data preservation txt file , Or manually copy to txt, By default, a tab Column interval .

2. stay mysql Create tables in the database ：create table table_name ...

3. Use mysql Of load Command to import local data ：

load data local infile"path" into table table_name fields terminated by '\t'; //path Is the file path ,table_name Is the name of the watch

perhaps

load data local infile"path" into table table_name("field1","field2"...); //field Is the corresponding field name

Be careful , If the inserted encoding format is incorrect , It may appear that the record is null perhaps “?” The situation of , here , Need to put txt Change the code to utf-8（ Use editplus, Or notepad , adopt “ Save as ”, Set encoding ）.

If you use the method of primary key self increment （auto_increment）, When you import data incorrectly for the first time , Delete （delete）, Re import , Will find key The value of is no longer from 1 Start （ The previous serial number has been used ）. If necessary , Before re importing , adopt “alter table table_name auto_increment=1” command , To modify the auto_increment The initial value of the .

Besides , Import data , There may be format problems , If there are spaces at the beginning and end , You can use trim function ：

update table_name set field = trim(field);

You can even choose to remove the left or right space ,ltrim,rtrim;

If you want to remove all spaces , You can use replace function ：

update table_name set field = replace(field, ' ', '');