MySQL Invalid UTF8 character string when importing csv table

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

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;

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

and change the file Encoding to UTF-8

then import your csv file