Can't debug this simple SQL Statement

Question

I'm importing data into a MySQL table using LOAD DATA LOCAL INFILE. I.ve done this on another table without difficulty, and I copied the stmt and just changed the table name and the fields. Here's the statement:

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id, league_season_id, player_id);

Here's the error:

MySQL said: Documentation

#1064 - 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 ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id,' at line 4

The FIELDS TERMINATED BY and LINES TERMINATED BY lines are exactly the same as my previously working statement. I've checked the structure and it's fine.

Table Structure

I'd appreciate someone pointing out where I'm screwing up. Thanks

• RDFozz: When I look this command up, the IGNORE *N* LINES clause seems to come after the FIELDS and LINES clauses. It's not clear if this matters, but it often does. Could you try it with that clause moved?

Answer 1

The order of the options matter.

Once you placed IGNORE 1 LINES, the next thing should be the list of columns.

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, location_id, game_id, league_season_id, player_id);

See the MySQL Documentation on the syntax and you will see the correct order for the options.