MySql Load Data Local Syntax?

Question

I have a text file "result.txt" that used pipes '|' to separate out the fields. I used PhpMyAdmin and successfully imported it to my table by specifying using "CSV LOAD DATA" and telling it the fields should be separated by '|'.

PhpMyAdmin also gave the full query for it, so I copied it and paste it into my php script, which looked like this:

 mysql_query("LOAD DATA LOCAL INFILE 'C:/wamp/www/TouchStone/result.txt' INTO TABLE customer_change FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' ")
 or die(mysql_error());

I will always receieve error saying:

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 ''' at line 2

I was wondering, since I copied exactly the same query generated by phpmyadmin, I think it will definitely work here. But why will such error happen?

I tried trimming the query to contain only "FIELDS TERMINATED BY " and it worked. But the database populated this way will contain incorrect data. So I am really wishing to learn why would the original longer query would fail?

Thanks.

Marc B · Accepted Answer · 2011-08-05 17:41:48Z

2

You're using a double-quoted string, so the \r\n will be seen as literal carriage return and line feed characters. You'll need to double-escape them as well: \\r\\n.

The "on line 2" in the error message is evidence of this - there's no actual second line if your query, but because of the embedded newline/carriage return, there is once it gets to MySQL.

answered Aug 5, 2011 at 17:41

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Over a year ago

I changed that to double-escape, but I got following error: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 ''customer_change' FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\' at line 1

Kevin Over a year ago

yeah, I am totally lost here too :(

Pravin Gadekar · Accepted Answer · 2011-08-05 17:10:09Z

0

Try this :

mysql_query("LOAD DATA LOCAL INFILE 'C:/wamp/www/TouchStone/result.txt' INTO TABLE customer_change FIELDS TERMINATED BY '|' ESCAPED BY '//' LINES TERMINATED BY '/r/n' ")
 or die(mysql_error());

answered Aug 5, 2011 at 17:10

Pravin Gadekar

1111 silver badge3 bronze badges

1 Comment

Kevin Over a year ago

I got different error:Field separator argument is not what is expected; check the manual

Collectives™ on Stack Overflow

MySql Load Data Local Syntax?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related