MySQL load data infile ; incorrect string value error

Question

I am using MySQL workbench to create database from EER diagram (forward engineering), and want to load data from a text file to my table

load data infile 'c:\\actors.txt' ignore into table actors field terminated by '\t';

the file contains European characters. I have tried utf16,utf8,latin1,latin2 character sets for my database but still receiving error incorrect string value '\xE4vel'. I have dropped schema and created each time with different character set (all tables have correct collate according to character set). my os is windows7 64bit if it makes any difference! any one can help me. thanks

Brian Logan · Accepted Answer · 2013-10-03 17:51:40Z

1

Have you tried using

LOAD DATA LOCAL INFILE?

Also, I don't know what is with the random Ignore after your file location.

For instance, you would just add the word LOCAL after Data, and take out the ignore. Try this:

LOAD DATA LOCAL INFILE 'c:\\actors.txt' into table actors field terminated by '\t';

answered Oct 3, 2013 at 17:51

Brian Logan

8422 gold badges6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amir-Mousavi Over a year ago

that ignore works kinda like local , ignores errors (doesn't stop loading) and continue the rest. I tried local and still the same problem, all the special characters are replaced by ? in the database

Brian Logan Over a year ago

Hmmm, very strange. Special characters as in what? And what are you going to use this data as later? You might need to transfer the special characters to HTML code, save it like that, then when using it, transfer it back.

Amir-Mousavi Over a year ago

characters like ö,ä,å,á,Ó,... this is a simple movie database with tables of actors directors writers producers and ...

Brian Logan Over a year ago

Try using mysqli_real_escape_string(), so for instance, mysqli_real_escape_string($data)

Amir-Mousavi · Accepted Answer · 2013-10-04 09:48:44Z

0

I managed to fix it , it seems that workbench (at least in my case) does not define charset and collate of each column in tables as it is defined in the database and you should do it manually!! for example if you set your database charset latin1 and collate latin1_swedish_ci each field in each table still remains empty of charset and collate , I had to go 1 by 1 to my tables and set the columns charset ! I dont know if it is the problem of forward engineering or what ? any way it solved

answered Oct 4, 2013 at 9:48

Amir-Mousavi

4,65315 gold badges79 silver badges135 bronze badges

Collectives™ on Stack Overflow

MySQL load data infile ; incorrect string value error

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related