CODEWITHSUNDEEP
mysqlsqlcsvimport
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

Import CSV into MySQL - Offset by 1 Column

I am importing my data from a csv file like this:

LOAD DATA LOCAL INFILE "c:\myfile.csv" INTO TABLE historic FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

This ALMOST works perfect. My table has one different row, the first: An auto incremented id column. Otherwise the csv file and the MySQL table match perfectly. When I run the command above, it puts the first column from the csv into the id field. It want it to actually go into the second.

Is there a way I can modify the above statement to either specify the columns or just offset it by 1? (skipping the first column on import).

Upvotes: 0

Views: 632

Answers (2)

vinibarr
vinibarr

Reputation: 498

You can load your data specifing the order columns that you're going to use into your table: LOAD DATA LOCAL FILE '/tmp/test.txt' INTO TABLE historic (name_field1, name_field2, name_field3...);

Or if you prefer you can load it first to a temporary table and use a select into statement to laod it into your final table (it's slower).

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562310

You can optionally name the columns to be populated by LOAD DATA, and simply omit your id column:

LOAD DATA LOCAL INFILE "c:\myfile.csv" INTO TABLE historic 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 
(column2, colum3, column4, column5, ...)

Upvotes: 2

Related Questions