1

So I have a CSV file that contains non UTF-8 text and I want to load data to a table n my database, for that I use:

$con->query("LOAD DATA INFILE '$csv' INTO TABLE bdi_csv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n';");

But I get the following error:

Error: Incorrect string value: '\xF3n'

My table and database have UTF-8 charset and the CSV file is non UTF-8 (in Notepad++ shows ANSI as encoding).

EDIT: Thanks to @Fred -ii- by his comment: open the CSV file and use "convert to UTF-8" (Notepad++ Encoding)

Gerardo Charles Rojas Vega
  • 633
  • 7
  • 16
  • What is the charset of your file? – Ohgodwhy Aug 28 '14 at 16:30
  • Try escaping it with `BY '\\n'`. or `BY '\"\n\"';` the latter I'm not 100% sure of though. – Funk Forty Niner Aug 28 '14 at 16:30
  • @Fred -ii- Yeah I didn't notice that (I'll edit that part), but the error is another.... – Gerardo Charles Rojas Vega Aug 28 '14 at 16:37
  • Your input data may not be utf-8. You will need to convert the input strings from windows-1252 to utf-8 (or to unicode), as per http://stackoverflow.com/a/948332/ – Funk Forty Niner Aug 28 '14 at 16:37
  • 1
    Save the file as UTF-8 in Notepad++ / under "Encoding", then "convert to..." for a quick test. – Funk Forty Niner Aug 28 '14 at 16:40
  • Have a look at [mb_convert_encoding](http://php.net/manual/en/function.mb-convert-encoding.php) there's even a comment in there directly related to your situatoin. :) – Ohgodwhy Aug 28 '14 at 16:40
  • @Fred -ii- I tried "convert to..." to get UTF-8 and it lets me handle as UTF-8 and everything nice, just is there a way to don't make it manually? – Gerardo Charles Rojas Vega Aug 28 '14 at 17:00
  • @Ohgodwhy using mb_convert_encoding you mean I need to buffer the data and then create insert statements, rigth? (is there a way to use LOAD DATA? in order to make it kind of directly) – Gerardo Charles Rojas Vega Aug 28 '14 at 17:01
  • Try adding `CHARACTER SET UTF8` to your code. I.e.: `("LOAD DATA INFILE '$csv' INTO TABLE bdi_csv FIELDS CHARACTER SET UTF8 TERMINATED BY ',' LINES TERMINATED BY '\n';")` – Funk Forty Niner Aug 28 '14 at 17:04
  • @Fred -ii- I've tried copying that line but not recognizes that sintax, I guess that would be if the problem is in DB. But as I've tried with "convert to...", so DB is managing rigth the data from the UTF8 file but I mean how get it from the file non-UTF8 or how to make php change it (just asking if there's another way I like the "convert to ..." way) – Gerardo Charles Rojas Vega Aug 28 '14 at 17:10
  • That I couldn't say for sure on how to do it Charles. Let's see if `Ohgodwhy` knows something more, or somebody else. Sorry I couldn't be of more help. See this Q&A http://stackoverflow.com/q/4957900/ might be something in there. – Funk Forty Niner Aug 28 '14 at 17:12
  • @Fred -ii- Don't worry thank you for all, just pointing the rigth sintax `("LOAD DATA INFILE '$csv' INTO TABLE bdi_csv CHARACTER SET UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';")` (the previos syntax error cause the order of CHARSET, however as I wrote my principal problem is with the CSV file) – Gerardo Charles Rojas Vega Aug 28 '14 at 17:19
  • I was close! So it worked out then, great. Thanks for the update, *cheers* – Funk Forty Niner Aug 28 '14 at 17:24

0 Answers0