CODEWITHSUNDEEP
mysqlcsvphpmyadmin
MC9000
MC9000

Reputation: 2423

Importing a CSV file using query for MySQL (specifying columns)

I was trying to import a CSV file into a table with an auto-incrementing column named "id". I do not want to insert the id column as it numbers itself, but after many tries I cannot get this to work. Here is the query I tried:

LOAD DATA LOCAL INFILE 'C:\\CaliberMap.csv'
INTO TABLE sefeed_calibermap 
(SiteCaliber,SE1Caliber,SE2Caliber,Other1Caliber,Other2Caliber,Other3Caliber,Other4Caliber)
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

When I execute the query, I get the following message:

#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 'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMI' at line 4

I'm stumped! I wrote this query after reading examples from many different places on the Internet, so the syntax looks correct.

I'm running PhpMyAdmin version 5.6.10.

Upvotes: 2

Views: 14191

Answers (1)

Don
Don

Reputation: 2377

You are confused I think by the documentation you have read. Try this:

LOAD DATA LOCAL INFILE 'C:\\CaliberMap.csv'
INTO TABLE sefeed_calibermap     
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(SiteCaliber,SE1Caliber,SE2Caliber,Other1Caliber,Other2Caliber,Other3Caliber,Other4Caliber);

The table fields should be at the end.

Upvotes: 7

Related Questions