MySQL LOAD DATA INFILE issue with special characters

Question

Im having problems with LOAD DATA LOCAL INFILE loading a utf8 file. The Query looks like this:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE artikel CHARACTER SET UTF8
FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

But fields with special characters like "Dämmung" will be cut to "D".

SHOW VARIABLES LIKE '%_set_%';

Shows that it's all set to utf8, and I even used notepad++ to make sure the file is utf8.

It would really help, if someone knows advice, it's a huge file and I would like to avoid entering the wrong fields manually :-/

Solution 1

I was having the same problem. There is a "hack" that solves this: maitain everything with UTF8. The exception is with the "LOAD DATA LOCAL....". When I use LATIN1 instead of UTF8 it works fine.

Solution 2

It worked for me.

LOAD DATA LOCAL INFILE 'D:/File.csv'
INTO TABLE my_table
CHARACTER SET latin1 
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n'