CODEWITHSUNDEEP
mysql
Kimomaru
Kimomaru

Reputation: 1023

Load Data Infile on MySQL Syntax Error

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

Upvotes: 0

Views: 2053

Answers (2)

Fueled By Coffee
Fueled By Coffee

Reputation: 2559

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,
...

Upvotes: 1

VMai
VMai

Reputation: 10336

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,...]

Upvotes: 3

Related Questions