MySQL Invalid UTF8 character string when importing csv table

Question

I want to import a CSV file into a MySQL database:

load data local infile 'C:\\Users\\t_lichtenberger\\Desktop\\tblEnvironmentLog.csv' into table tblenvironmentlog character set utf8 fields terminated by ';' lines terminated by '\n' ignore 1 lines; 

. The CSV file looks like this:

But I get the following error, and I cannot explain why:

 Error Code: 1300. Invalid utf8 character string: 'M' 

Any suggestions?

Answer 1

See what settings for export were. Search for "UTF-8."

This means that "Truncated text" is caused by the fact that the data is not encoded as utf8mb4. Outside of MySQL, “look for“ UTF-8. ”(Internally, MySQL, utf8, and utf8mb4 work equally well for all European character sets, so ü shouldn't be a problem.

If it was exported as "cp1252" (or any number of encodings), the byte for ü not valid for utf8mb4, which will result in truncation.

If this analysis is correct, there are two solutions:

Plan A: Export as UTF-8 .

Plan B: import as latin1 . (You do not need to change the definition of the column / table, just LOAD DATA .)

Answer 2

He complains about 'M' , but I think this is in München , and the actual problematic character is next, umlaut 'ü' .

One easy way to check is to try downloading the file from only the first two lines and see if it works. Then add the third line, try again and see if it worked.

If you cannot or do not want to replace these special characters in your data, you will need to start searching for the character sets configured in the CSV file, database, table, columns, tools, etc.

Are you using MySQL 5.7 or higher? Then try to change something simple to character set utf8mb4 in your load data command.

See How MySQL 5.7 Handles "utf8mb4" and "Load Data" for a similar problem.

See also:

import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

The problem with utf8 characters; I do not see what I kept

"Invalid string value" when trying to insert UTF-8 into MySQL via JDBC?

Answer 3

Just open the csv file in a text editor (e.g. Nodepad ++)

and change the Encoding file to UTF-8

then import the csv file