(MySQL) Load data infile specific columns

Question

I have a problem loading data into specific columns of an table. The CSV file is build dynamic with the default fields ID, LAST_REFRESH, ALIAS1 and may contain ALIAS2 to ALIAS8. Current CSV only contains ALIAS1-4

The MySQL table contains the columns ID, LAST_REFRESH, ALIAS1-ALIAS8. My code for the first file already fails. Code after variables are set is:

LOAD DATA LOCAL INFILE 'C:\\temp\\\OSS001'
INTO TABLE REJECTS (ID, REFRESH_DATE, ALIAS1, ALIAS2, ALIAS3, ALIAS4)
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

But unfortunately i still receive the following error:

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 '"' LINES TERMINATED BY '\n'

Anybody knows what i'm doing wrong?

fancyPants · Accepted Answer · 2015-01-20 09:01:44Z

16

The column names have to be specified last. Read more about it here.

LOAD DATA LOCAL INFILE 'C:\\temp\\\OSS001'
INTO TABLE REJECTS 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, REFRESH_DATE, ALIAS1, ALIAS2, ALIAS3, ALIAS4, ALIAS5, ALIAS6, ALIAS7, ALIAS8)

answered Jan 20, 2015 at 9:01

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

2 Comments

fancyPants Over a year ago

I think you may have to catch this in your application logic. Not on database layer.

Robin Over a year ago

Already did it. I added a variable in the application that group_concats the alias fields :)

Jens · Accepted Answer · 2015-01-20 09:01:49Z

2

Remove the columns:

LOAD DATA LOCAL INFILE 'C:\\temp\\\OSS001'
INTO TABLE REJECTS FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

answered Jan 20, 2015 at 9:01

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

(MySQL) Load data infile specific columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related