CODEWITHSUNDEEP
mysqlutf-8special-characters
user3181103
user3181103

Reputation: 113

MySQL LOAD DATA INFILE issue with special characters

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 :-/

Upvotes: 7

Views: 16215

Answers (5)

Ahmed El-Atab
Ahmed El-Atab

Reputation: 681

Please take care about the collation type of the database and table columns!

I'm using the utf8mb4_unicode_520_ci for both database and table columns.

Everything worked well with keeping CHARACTER SET utf8 in the query.

Upvotes: 0

Junaid Munir
Junaid Munir

Reputation: 61

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'

Upvotes: 6

Abderrahmane BECHIKH
Abderrahmane BECHIKH

Reputation: 653

Here is my solution when I was troubleshooting this problem of a special characters:

I will explain:

  • you can change ; to , because I used csv ; separated not like you
  • I had line breaks inside my columns so I escaped with \\

The script is :

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'

Upvotes: 1

Marat
Marat

Reputation: 15738

Unfortunately, LOAD DATA does not accept utf8 input, no matter what charset is passed on cli options and used by the table. I didn't find it in documentation and assume it is a bug.

The workaround is to use mysqlimport instead. The shortcoming of this solution is that if you want to disable foreign key check during import (doesn't apply to MyISAM tables) you will have to disable it globally, which requires SUPER privilege:

echo "SET GLOBAL FOREIGN_KEY_CHECKS=0;" | mysql -u root -p

Upvotes: 0

Gustavo Tarchiani
Gustavo Tarchiani

Reputation: 203

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.

Upvotes: 7

Related Questions