LOAD DATA INFILE has LINES TERMINATED BY problem?

[Richard Creech - DreamRiver.com]

Hello All,

Problem Synopsis: 
I am importing a CSV file and need help with 'line terminated with" syntax or ???

Detail
I have a simple situation and question. I wish to import a comma delimited file (CSV) 
from an Excel like file generated by a Windows operating system into a mySQL database 
on a linux system.

The problem I experience is that the second (2nd) record and all later records are 
scrambled when I use the normal mySQL command of LOAD DATA INFILE with a php script. I 
do NOT have access to the mysqlimport or any other built in interfaces. My commands 
must flow from a php script. 

It appears that the end of line delimiter is not being correctly interpreted. The 
first record is correctly read and inserted into the table. The subsequent lines are 
not.

This makes me think that the query is close to ok. This is the query:

LOAD DATA INFILE '/home/fpo/htdocs/secure/upload/export-customers.csv' REPLACE INTO 
TABLE tempCustomer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' 
LINES TERMINATED BY '\r' (customerid, custLastName, custFirstName, custEmail, 
deliveryDay, storeid)

I have tried many combinations, including LINES TERMINATED BY '\r\n' but I just can't 
seem to get this to work.

All I'm trying to do is to read data into the database using a basic internal 
command... the archives and manual have helped clarify many aspects, but I haven't 
resolved the core issue.

Can you identify the problem?

Any ideas at this point would be most welcome.

Thank you. 

Regards,

Richard Creech
[EMAIL PROTECTED]
250.744.3350 Pacific Time Canada
Dynamic website content with phpYellow Pages
http://dreamriver.com/software/index.php3



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php