3

I'm trying to import a CVS file where I get this warning: 1366 Incorrect string value: '\x96 PART...' for column

I read somewhere that this is about the 4-bit utf8 characters. But changing the collation of the table and column into utf8mb4 didn't work either.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
Iraj
  • 41
  • 1
  • 4
  • 1
    Possible duplicate of [How to fix "Incorrect string value" errors?](http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors) – Phiter Feb 25 '16 at 23:11
  • How are you importing the file exactly? What encoding is the file in? What is the **connection encoding** to your database? – deceze Feb 26 '16 at 08:43
  • @PhiterFernandes, I saw that question and answer, but I also noticed that there is an argument that the mentioned method changes the database itself: http://stackoverflow.com/a/11013986/3316750 – Iraj Feb 26 '16 at 20:30
  • @deceze, I do not know the answer to those questions. I'm searching know to figure out how I can learn the encoding of a CSV file and the database connection encoding. – Iraj Feb 26 '16 at 20:32

1 Answers1

4

The hex 96 is presumably the latin1 encoding for an en-dash (–). But you have specified that the CSV file is utf8-encoded (or utf8mb4), this character is incomprehensible to utf8.

Plan A: Change the file. (This is probably not practical.)

Plan B: Tell MySQL that the file is latin1 (as opposed to utf8). Then MySQL will convert it correctly to the utf8-encoding E28093.

"Collation" has to do with sorting and comparing; "Character set" has to do with 'encoding'.

Add this to the LOAD DATA statement that I assume you are using:

CHARACTER SET latin1

Reference.

Rick James
  • 135,179
  • 13
  • 127
  • 222