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 '
' 
 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

Answer 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 '
' 

 (`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).