Different column names in db and csv file while LOAD DATA INFILE

Question

I am trying to load data into MYSQL (in MAMP) using LOAD DATA LOCAL INFILE. Some of the data elements specified in the database table are different from the column names in the data files (multiple csv files). For ex: the database has a column 'location_affected' and the data file has a column name 'location_name' for the same data element. Also, the data files not not have the same number of columns nor the same column names all through. Each data file might have around 30-40 columns out of the ~200 data elements specified in the database table. Could anyone suggest how to map the column names between the db table and the csv file? I tried the following but it throws an error: "location_name does not exist"

LOAD DATA LOCAL INFILE 'pathtothefile' INTO TABLE tablename 
 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
 LINES TERMINATED BY '\r'
 IGNORE 1 LINES 
(col1,col2,col3..............)
SET location_name = location_affected ;

Joshua Martell · Accepted Answer · 2012-11-14 04:11:03Z

2

You can skip columns by assigning them to dummy variables:

LOAD DATA LOCAL INFILE 'pathtothefile' INTO TABLE tablename 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
IGNORE 1 LINES 
(col1,col2,@skipme,@skipme,col3..............);

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

answered Nov 14, 2012 at 4:11

Joshua Martell

7,2122 gold badges33 silver badges37 bronze badges

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

Comments

tadman · Accepted Answer · 2012-11-14 03:55:54Z

1

The mapping is defined in your LOAD DATA statement. If you want to re-order the columns you should adjust them there to match whatever is present in your CSV file.

The IGNORE 1 LINES part is what skips the typical CSV header labels. These aren't used by the import procedure.

In this case, SET location_name=location_affected makes no sense. If you want to import into the location_name column, specify that in your definition.

answered Nov 14, 2012 at 3:55

tadman

212k23 gold badges237 silver badges266 bronze badges

Comments

The mapping is defined in your · Accepted Answer · 2013-05-15 19:10:46Z

-1

The mapping is defined in your LOAD DATA statement. If you want to re-order the columns you should adjust them there to match whatever is present in your CSV file.

The IGNORE 1 LINES part is what skips the typical CSV header labels. These aren't used by the import procedure.

In this case, SET location_name=location_affected makes no sense. If you want to import into the location_name column, specify that in your definition

answered May 15, 2013 at 19:10

The mapping is defined in your

71 bronze badge

Collectives™ on Stack Overflow

Different column names in db and csv file while LOAD DATA INFILE

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related