LOAD DATA

[superfriend]

Hi.  I am running WOS portable as my server.  The following script runs in phpAdmin in the SQL window.  But when placed in a .php file and the php file is run, it complains.

 

$sql = "LOAD DATA LOCAL INFILE 'E:\\php\\www\\folder\\file.csv'

INTO TABLE test_csv

FIELDS TERMINATED BY ',' 

ENCLOSED BY '\"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES ";

 

$result = mysql_query($sql) or die(mysql_error());

 

Will be back to add error message.

[superfriend]

Error Message:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' IGNORE 1 LINES' at line 6

 

I spent hours today looking at this, reviewing a number of sites.  One idea I think the problem might be is the double quote delimiter.  ENCLOSED BY '\"' is

 

single quote

backslash

double quote

single quote

 

without the backslash, the double quote ends the string.

 

Suggestions please.

Erik.

[Pikachu2000]

What happens if you echo the query to the screen, then copy/paste it into phpMyAdmin?

[superfriend]

Thank you for that suggestion.  Echoing caused it to fail in phpAdmin.  I was able to see what SQL thought the query was.  I had unintentionally escaped a single quote, leaving an unfinished string.  That led to realizing I needed a lot more escape characters.  The final result is this:

 

<?php

 

$sql = "LOAD DATA LOCAL INFILE 'E:\\\\php\\\\www\\\\folder\\\\file.csv' "

."INTO TABLE test_csv "

."FIELDS TERMINATED BY ',' "

."ENCLOSED BY '\"' "

."ESCAPED BY '\\\\' "

."LINES TERMINATED BY '\\r\\n' "

."IGNORE 1 LINES ";

 

echo $sql;

 

?>

 

Thank you very much.