MySQL Invalid UTF8 character string when importing

Question

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:

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

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

Any suggestions?

Answer 1

Just open the csv file in your text editor (like Nodepad++)

and change the file Encoding to UTF-8

then import your csv file

Answer 2

It's complaining about 'M' but I think it's in München and the actual problematic character is next one, the umlaut 'ü'.

One simple way to test would be to try loading a file with just the first 2 rows & see if that works. Then add the 3rd row, try again & see if that fails.

If you can't or don't want to replace these special characters in your data, then you'll need to start investigating the character sets configured in your CSV file, database, table, columns, tools etc...

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

See How MySQL 5.7 Handles 'utf8mb4' and the Load Data Infile for a similar issue.

Also see:

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

Trouble with utf8 characters; what I see is not what I stored

“Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?

Answer 3

Nothing else I tried worked for me, including ensuring that my .csv was saved with UTF-8 encoding.

This worked:

When using LOAD DATA LOCAL INFILE, set CHARACTER SET latin1 instead of CHARACTER SET utf8mb4 as shown in https://dzone.com/articles/mysql-57-utf8mb4-and-the-load-data-infile

Here is a full example that worked for me:

TRUNCATE homestead_daily.answers;
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE homestead_daily.questions;
SET FOREIGN_KEY_CHECKS = 1; 
LOAD DATA LOCAL INFILE 'C:/Users/me/Desktop/questions.csv' INTO TABLE homestead_daily.questions
CHARACTER SET latin1
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(type, question, created_at, updated_at);
SELECT * FROM homestead_daily.questions;

Answer 4

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.)