Syntax Error on mysql php load data infile

Question

This is my first time trying to import data from csv file into mysql table.

The problem is that the order of csv columns doesn't match the order in table and also there is one column (auto increment) id missing from csv. I am trying to do this but it gives me syntax error.

        $sql="LOAD DATA INFILE '$file' 
 INTO TABLE calls 
 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
 OPTIONALLY ENCLOSED BY ".'"'." 
 (`account_code`,`source`,`destination`,`dbcontext`,`caller_id`,`channel`,`destination-channel`,`lastapp`,`lastdata`,`start`,`answer`,`end`,`duration`,`billseconds`,`disposition`,`amaflag`,`call_id`,`userfield`)";

Notice the $file is the file name dynamically generated. I get this error upon execution of the query.

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 'OPTIONALLY ENCLOSED BY " (`account_code`,`source`,`destination`,`dbcontext`,`' at line 4

Any idea why?

Thanks Ahmar

Shouldn't the enclosed by clause have two quote characters? Looks you've only got one — Clive
– Clive, Commented May 26, 2014 at 11:03

Clive · Accepted Answer · 2014-05-26 11:21:23Z

1

This:

OPTIONALLY ENCLOSED BY "

Needs to be

OPTIONALLY ENCLOSED BY '"'

So your code should change to

$sql="LOAD DATA INFILE '$file' 
 INTO TABLE calls 
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
 LINES TERMINATED BY '\r\n' 

 (`account_code`,`source`,`destination`,`dbcontext`,`caller_id`,`channel`,`destination-channel`,`lastapp`,`lastdata`,`start`,`answer`,`end`,`duration`,`billseconds`,`disposition`,`amaflag`,`call_id`,`userfield`)";

(adding the required single quotes and swapping the order of clauses is all that's changed, but I also changed your concatenation to something a bit more readable).

edited May 26, 2014 at 11:21

answered May 26, 2014 at 11:14

Clive

36.9k8 gold badges89 silver badges113 bronze badges

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

3 Comments

Clive Over a year ago

Try the edit, you had things slightly the wrong way round (ENCLOSED BY refers to the fields, not the lines). That query no longer gives me a syntax error so you should be good to go

Ahmar Ali Over a year ago

By the way on my local server I am giving the script an absolute path like this C:\wamp\tmp\php790E.tmp but it automatically goes to bin folder and gives me this error c:\wamp\bin\mysql\mysql5.6.12\data\wamp mpphp790E.tmp' not found (Errcode: 22 - Invalid argument) Do you know how to avoid this?

Clive Over a year ago

I'm afraid I don't - I use *nix and always use an absolute path (/path/to/file); I haven't run into that problem before, but it seems like your path is being interpreted as a relative one

Collectives™ on Stack Overflow

Syntax Error on mysql php load data infile

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related