Search code examples
mysqlcsvutf-8character-encoding

MySQL Invalid UTF8 character string when importing csv table

I want to import an .csv file into MySQL Database by:

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: enter image description here

But I am getting the following error and I cannot explain why:

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

Any suggestions?

Solution

  • See what the settings for the export were. Look for "UTF-8".

    This suggests that "Truncated text" is caused by the data not being encoded as utf8mb4. Outside MySQL, "look for "UTF-8". (Inside, MySQL, utf8 and utf8mb4 work equally well for all European character sets, so the ü should not be a problem.

    If it was exported as "cp1252" (or any of a number of encodings), the byte for ü would not be valid for utf8mb4, leading to 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 column/table definition, just the LOAD DATA.)