*. Errors such as garbled characters when importing csv files to Mysql

You can use load data infile to import a *. csv file to mysql,

Summary:

Load data in file Load data [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.csv'
[REPLACE | IGNORE]
Into table tbl_name
[FIELDS
[Terminated by 'string']
[[OPTIONALLY] enclosed by 'Char ']
[Escaped by 'Char ']
]
[LINES
[Starting by 'string']
[Terminated by 'string']
]
[IGNORE number LINES]
[(Col_name_or_user_var,…)]
[SET col_name = expr,…)]

 

For detailed syntax usage, see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data

If a table contains only English characters, the import is not a problem and the writing is extremely simple:

Load data local infile 'd: \ ports.csv 'into TABLE ports;

 

Problem 1:The ports.csv file contains Chinese characters, which are garbled after data is imported.

First, modify the table and field encoding method.

Alter table ports character set gbk2312;
Alter table ports modify port_chinese varchar (50) character set gbk2312;

 

 

You can use show create table ports to view the table and field encoding methods. You can also use a visualization tool to modify the field encoding format.

The command line I imported is written as follows:

Load data local infile 'd: \ ports.csv 'into TABLE ports fields terminated ', 'enabledby' "'Lines terminated by' \ r \ n' starting '';

 

However, after I modify the preceding two items, the imported data is still garbled. The CSV file encoding format is GBK,

Find an article on the Internet about this: http://www.sqlstudy.com/ SQL _article.php? Id = 2008081901. For reference, modify the statement as follows:

Load data local infile 'd: \ ports.csv 'into TABLE portsCharacter SetGbk2312Fields terminated ', 'enabledby' "'Lines terminated by' \ r \ n' starting '';

 

At this point, garbled characters are converted into correct Chinese characters.

 

Problem 2:When viewing the data after the data is imported, the first line of valid data in the CSV file is lost, but there are three more lines of weird invalid data lines. There are 39 warnings when executing the import command.

First, I used the show warnings; command to view the warning content of the prompt. I found that N + WARNINGS were reported when the column title in the CSV file was imported into the database, the key reason for a data error lies in the line terminated by '\ r \ n', because the first row and column titles do not end with a line break BY carriage return, an error occurred while converting the title column of the first row, which also caused the error of the valid data of the first row during the import process. I add the title of the last column of the file title in the csv file and press enter to save the data. The first row of valid data in the CSV file is finally imported into the data, but there is one more row of header data, the IGNORE command can solve this problem.

The final syntax for importing CSV files to the command line is as follows:

Load data local infile 'd: \ ports.csv 'into TABLE ports character set gbk2312 fields terminated ', 'enabledby' "'Lines terminated by '\ r \ n' starting BY ''ignore 1 LINES;