MySQL infile ignore header row

Question

Trying to get MySQL to ignore a specific row when importing through an INFILE command. Essentially, it's the "header" row in the CSV file.

LOAD DATA LOCAL INFILE 'C:\myfile.txt' REPLACE INTO TABLE my_db.my_table;

I've looked in to the ignore command, but that's for ignoring duplicate keys. Is there a way to ignore a specific entry/row in the .txt file?

score 21 · Accepted Answer · edited May 25 '20 at 07:37

21

IGNORE has two meanings in a LOAD DATA INFILE statement. The first is replace / ignore when it comes to duplicate key errors. The second meaning is the one you're looking for. the complete statement is IGNORE n LINES

You can't skip a random line but you can skip n lines at the head of your inputfile like this:

LOAD DATA LOCAL INFILE 'C:\myfile.txt' REPLACE INTO TABLE my_db.my_table IGNORE 5 LINES;

This ignores the first 5 lines, where end of line EOL is defaulting to \n and can be set to any character using LINES TERMINATED BY 'CHR'

edited May 25 '20 at 07:37

this

137
1
6

answered Nov 26 '12 at 16:28

Michel Feldheim

17,625
5
60
77

So, if it were `IGNORE 5 LINES` would it ignore the 5th line or would it ignore the first 5 lines? – Plummer Nov 26 '12 at 16:36
this would ignore the first 5 lines, where end of line ( EOF ) is defaulting to "\n" and can be set to any character using `LINES TERMINATED BY 'CHR'` – Michel Feldheim Nov 26 '12 at 16:38

score 11 · Answer 2 · answered Nov 26 '12 at 16:25

11

Add this option to your statement:

IGNORE 1 LINES

answered Nov 26 '12 at 16:25

Tom

6,593
3
21
42

I like this answer, very straightforward. – caiosm1005 Mar 13 '16 at 16:38

MySQL infile ignore header row

2 Answers2