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

Question

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.

If you give us a couple of sample lines from the CSV file, and perhaps also the MySQL table description, we might be better able to help. As an alternative, try using HeidiSQL instead of PhpMyAdmin: it has a wizard to import CSV files, which can help. (HeidiSQL is a desktop program for managing MySQL databases, and I find it a lot easier to use than PhpMyAdmin.) — TRiG
– TRiG, Commented Mar 27, 2013 at 16:59

Don · Accepted Answer · 2013-03-24 12:09:40Z

7

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.

answered Mar 24, 2013 at 12:09

Don

2,4172 gold badges17 silver badges17 bronze badges

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

2 Comments

ChrisLively Over a year ago

This is correct per the load-data specs: dev.mysql.com/doc/refman/5.1/en/load-data.html

MC9000 Over a year ago

thanks folks! I'm new to MySQL (thanks to the new MS pricing MSSQL by the cpu core, sending the price into the stratosphere, I'm forced to move to MySQL)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related