Load Data Infile on MySQL Syntax Error

Question

I have a "load data" command that executes just fine, but I need to modify it to remove most of the fields from a CSV. I believe I've put it together correctly, but form some reason MySQL says that the syntax is not correct.

LOAD DATA INFILE '/path/file' INTO TABLE cdr_records (@dummy,
@dummy,
@dummy,
@dummy,
dateTimeOrigination, 
@dummy,
@dummy,
@dummy,
callingPartyNumber, 
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
originalCalledPartyNumber, 
finalCalledPartyNumber, 
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
pkid,
@dummy,
@dummy,
@dummy,
@dummy,
duration, 
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy) FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 2 LINES;

I get;

ERROR 1064 (42000): 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 ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 2 LIN' at line 104

The columns list goes after all the options, I believe. dev.mysql.com/doc/refman/5.1/en/load-data.html — Michael Berkowski
– Michael Berkowski, Commented Aug 21, 2014 at 20:05

VMai · Accepted Answer · 2014-08-21 20:05:48Z

3

It should be

LOAD DATA INFILE '/path/file' INTO TABLE cdr_records 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 2 LINES
(@dummy, ..., @dummy) ;

instead. First the FIELD and LINES clause then the list of the columns, not the other way around.

The syntax of LOAD DATA INFILE is:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

answered Aug 21, 2014 at 20:05

VMai

10.4k9 gold badges28 silver badges36 bronze badges

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

1 Comment

Kimomaru Over a year ago

thank you, that was the problem. Not sure why the solutiions I read said otherwise, but your answer did the trick. I appreciate your help.

Fueled By Coffee · Accepted Answer · 2014-08-21 20:07:07Z

1

Put your terminators and other options first

LOAD DATA INFILE '/path/file' INTO TABLE cdr_records
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 2 LINES
(@dummy,
@dummy,
...

answered Aug 21, 2014 at 20:07

Fueled By Coffee

2,5797 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

Load Data Infile on MySQL Syntax Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related