Problem importing text file into database 1

[jerijeri]

Hi,

I'm trying to set up a program to read a text file and import into MySQL. There are three pipe delimited fields.

This is the error message I get:

Database error: Invalid SQL: LOAD DATA LOCAL INFILE '/home/virtual/site2/fst/var/

http://www/html/temp/clients.txt'

INTO TABLE customer FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n' ('email', 'first_name', 'last_name');
MySQL Error: 1064 (You have an error in your SQL syntax near '\r\n' ('email', 'first_name', 'last_name')' at line 1)
Session halted.

The SQL statement works fine in PHPMyAdmin. If it was me using this, I'd just use PHPMyAdmin, but it's not. So we need a web-based upload.

I've tried escaping the \r\n and just trying \n by itself.
It doesn't work. I'm baffled as to why it works in PHPMyAdmin and not a PHP program.

Thank you,

Jer

 

[nigelcohen]

Jer

The code looks right except for the "ESCAPED BY" clause. There is already a default escape character of '\'. So your statement of ESCAPED BY '\' will cause MySQL problems understanding the first time it encounters a '\'.

If this is the problem, the solution is : ESCAPED BY '\\'

Try this and see if it works

 

[jerijeri]

Thanks for the help.

I had removed all the extra statements and it worked. So I didnt' investigate further. Your suggestion about the ESCAPED BY seems to pinpoint the culprit.

Thank you,

Jer