0

hy i d like to have some quesiton about the next lines . i want to read a delimited text wich is also have text qualifier

LOAD DATA INFILE 'D:/teszt2.txt' into table hamburger
    Fields terminated by ',' 
    Enclosed by '"'
    lines terminated by '\n'

and the file is loks like this

"650000",",103","DS SMITH RECYCLING GMBH","83064 RAUBLING, KUFSTEINER STR. 27",

and i made the colums and the table first and i wanted to read the whole big text into it. all of the columns are varchar(45) type but i get an error :

Error Code: 1366. Incorrect string value: '\xC1NIA S...' for column 'szallito_nev' at row 1 0.000 sec

best regards

balage90
  • 87
  • 1
  • 11
  • Which column is szallito_nev. Can you post table layout? Also is what you posted the entire line going in? – asantaballa Sep 26 '13 at 11:25
  • that is the 3th column the "DS SMITH RECYCLING GMBH" its hard to post it couse its contains 70 columns but all of them is varchar at first. basicly there was a hedaer line in the txt but i cut them off and after i made the colums from the header titles – balage90 Sep 26 '13 at 11:32
  • Hmmm. That does not seem to agree with the msg. It's complaining about a string with "'\xC1NIA S...". The hex C1 is an A with an accent mark so think this could be taken as special "bad" character. Can you search your input for the string "NIA S" (no \xC1) and see if you have a line like that? Then try changing the "\xC1" into an "A" and see if it loads the record. – asantaballa Sep 26 '13 at 11:39
  • yes thers a few line wich contains the words and what shuld i change? couse i didnt really understand :S "3500000",",137","HAMBURGER RECYCLING ROMÁNIA SRL" – balage90 Sep 26 '13 at 11:50
  • shuld be the problem is the Á char? – balage90 Sep 26 '13 at 11:56
  • Did you try to change to an "A" and load? Worked? I'd trust the string more then the line number because it's so specific. – asantaballa Sep 26 '13 at 12:00
  • I believe the accepting of special characters such as accented letters depends on character set you're using. But try quick edit to "A" just to see if that is the problems or not. – asantaballa Sep 26 '13 at 12:03
  • yes it did work so probalby the characterset is wrong couse after i changed to another column wher i use specific char like "é" becose of the hungarian language – balage90 Sep 26 '13 at 12:33
  • where shuld i set the character set? when i define the tabel or there is some way to define in the query? – balage90 Sep 26 '13 at 12:34
  • Have not dome myself. But look at http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html, http://dev.mysql.com/doc/refman/5.0/en/charset-configuration.html, and http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf. – asantaballa Sep 26 '13 at 12:36
  • Glad to! If it works out let me know so I can write up as answer to the problem here. – asantaballa Sep 26 '13 at 12:45
  • 1
    Now i figured out what was the problem ! i had to convert the source file into unicode 8 becouse it was ASCII before and after that it runs smoothly ! – balage90 Sep 30 '13 at 06:18

1 Answers1

0

I see an unpaired double quote (") in column two. could be taking rest of line as string.

"650000",",103","DS SMITH RECYCLING GMBH","83064 RAUBLING, KUFSTEINER STR. 27",
         ^

Should be 

"650000","",103","DS SMITH RECYCLING GMBH","83064 RAUBLING, KUFSTEINER STR. 27",
asantaballa
  • 3,919
  • 1
  • 21
  • 22
  • its part of the 2nd column couse its 0,103 but when the datas comes from the server they show up like this, thats why its ,103 so thats could be the problem? – balage90 Sep 26 '13 at 11:20
  • I see what you say. Then no, don't believe that is the problem. Looking again with your answer I see the string is correct, just looked funny. Will keep looking and see what I can come up with. Sorry! – asantaballa Sep 26 '13 at 11:24