We help IT Professionals succeed at work.

LOAD DATA SQL statement not working

Bill Sullivan
on
Hi all:
I'm triyng to load some data into a file in my MySQL database using the SQL query form in phpmyadmin.  The following is my query:

LOAD DATA INFILE \"/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv\" INTO TABLE ClientInfo FIELDS TERMINATED BY \',\' LINES TERMINATED BY \';\' -- phpmyadmin is adding the \' text in my query after I try to run it.  When I try to run this statement, I receive the following error:

#1064 - 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 '\"/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv\" INTO TABLE ClientIn' at line 1

Any help would be greatly appreciated!
Comment
Watch Question

Hmm thats odd, i just took our the \" and \' slashes and it works for me,

try this again...

LOAD DATA INFILE "/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv" INTO TABLE ClientInfo FIELDS TERMINATED BY ',' LINES TERMINATED BY ';'

if its still not working, what version of phpmyadmin are you using?

Author

Commented:
Nope.  Same problem - phpmyadmin adds the "\" after I type and send "go".  I'm running 3.1.3.2.
try replacing the double quotes with single quotes, not that it should make a difference but worth a test.

LOAD DATA INFILE '/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv' INTO TABLE ClientInfo FIELDS TERMINATED BY ',' LINES TERMINATED BY ';'

So just to check, your running this directly from phpmyadmin, not within another aplication?

Author

Commented:
Nope.  Didn't help.  Yes, I'm running this directly from phpmyadmin - not within another application.
Strange, the only way i can re-create this is by pasting it in with the slashes, doesnt add it in when i click go without them.

Just a long shot here, what browser are you in?

Author

Commented:
I'm using Safari.
Wouldnt think it would matter but maybe the browser is adding in the slashes on post rather than phpmyadmin, but that seems unlikely for Safari.

Your syntax appears correct, have you tried writing a php file (or other file format) to run just quickly test the sql, and to test if its phpmyadmin at all ?

Author

Commented:
Well, when I try to do this via a PHP statement in my program, I get the following error:

Query failed: Access denied for user 'ncr_bsullivan'@'127.0.0.1' (using password: YES)

Interestingly, I copied this page from another page that works, so I'm confident my user and password are correct and they have access to all the tables I'm trying to access.  Is it possible it's having trouble pulling data from my Hard Drive and uploading it to the web?  Could it be my Hard Drive that has denied access?

Here's the PHP code I used to try and upload the data:

$query = "LOAD DATA INFILE '/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv' INTO TABLE ClientInfo FIELDS
                                    TERMINATED BY ',' LINES TERMINATED BY ';'";
                              $result = mysql_query($query)
                                    or die('Query failed: ' . mysql_error());
hmm, is that error being generated from that bit of code there or connecting to the database?
If its from the code you have pasted here, its probably that your user doesnt have access to the csv file.

Try having the csv file local to your php file,
e.g.

$query = "
  LOAD DATA LOCAL
  INFILE '/Bill/Documents/RCSI/North Creek Roofing/MissingData.csv'
  INTO TABLE ClientInfo
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY ';'
";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());
sorry, i forgot to change the path of the csv file for the example, basicly the csv file would be a relative path from the php file.
e.g. ../../Bill/Documents/RCSI/North Creek Roofing/MissingData.csv
or wherever

Author

Commented:
Well, changing the code (and making the path "../Bill/Documents/RCSI/North Creek Roofing/MissingData.csv", I get the following error:

Warning: mysql_query() [function.mysql-query]: LOAD DATA LOCAL INFILE forbidden in /Library/WebServer/Documents/UploadData.php on line 64
Query failed:
is ../Bill/Documents/RCSI/North Creek Roofing/MissingData.csv definetly the correct relative path from the php file?
I have never seen that error before, and i cant find much on it on google im afraid.