Load data local file

[fran]

I have this query: "LOAD DATA LOCL INFILE '$txtfile' INTO TABLE $table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

 

when it loads the file into the database, it looks like this:

userid |    XY   | gondola_no |  date  | time   | processed  ÿþ1  | x123y132|    h123    |20140212| 082219 |       2  | x123y132|    h123    |20140212| 082219 |          |         |            |        |        |    N

If I copy the same data from the csv file and paste it into another csv file  and run it everything is perfect.

 

LOAD DATA LOCL INFILE '$txtfile' INTO TABLE $table CHARACTER SET UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

 

caused an error.

 

What am I doing wrong?

[mac_gyver]

it is likely that your file has been saved with the BOM (byte order mark) characters. use your programming editor to save it without the BOM. if the file itself doesn't contain any utf-8 encoded characters, just save it as an ansi/ascii encoded file.

 

also, that indicates your database column definitions are all probably a character type. your userid should be an integer, your date should be a date data type, and your time should be a time data type (or your date/time should be combined into one datetime data type.)

[fran]

The file is coming from a scanner program.  Is it possible to change the character type with php?

[mac_gyver]

a) what scanner program?

 

b) in there a chance you opened/closed the file in your programming editor, which could have added the BOM characters to the file?

 

c) you could always read the file using php, remove the leading BOM characters, then save the file.

[fran]

The program is Kalipso.  The issue is coming from the .csv file downloaded from scanner. When I open it, copy it to notepad and save it as .csv then run it again, it works fine.  So no, it isn't because I opened/closed the file.  I tried to use php to open, read and write the file but it doesn't change anything.  How do you remove the leading BOM characters when you don't really see them?