LOAD DATA LOCAL, How do I skip the first line?

Question

I'm trying to Load a CSV file into my MySQL database, But I would like to skip the first line.

I fact It contains the name of my columns and no interesting data.

Here is the query I'm using:

LOAD DATA LOCAL INFILE '/myfile.csv' 
INTO TABLE tableName
FIELDS TERMINATED BY ','
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
(column,column,column);

score 120 · Accepted Answer · answered Oct 24 '09 at 16:09

120

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

(reference)

answered Oct 24 '09 at 16:09

Zed

57,028
9
76
100

I have a question. Lets take IGNORE LINES 5; as an example. Would that ignore line 5 or lines 1-5? – EatSleepCode Jul 08 '19 at 16:05
@EatSleepCode, it will ignore the first N lines, so lines 1 to 5, this last included. – appartisan May 17 '20 at 12:48

score 46 · Answer 2 · answered Aug 02 '16 at 20:15

46

For those curious, IGNORE N LINES should be after the separator qualifiers:

LOAD DATA LOCAL INFILE '/myfile.csv' 
INTO TABLE tableName
FIELDS TERMINATED BY ','
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(column,column,column);

answered Aug 02 '16 at 20:15

Chad Geary

557
4
5

3

Your note on the placement of IGNORE is KEY! – elPastor Jul 05 '17 at 23:56

score 2 · Answer 3 · edited Nov 28 '19 at 09:54

2

Try this:

IGNORE N LINES
LOAD DATA INFILE "/path/to/file.csv"
INTO TABLE MYTABLE 
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

edited Nov 28 '19 at 09:54

1737973

159
18
42

answered Nov 28 '19 at 08:52

bharat bhagat

19
5

LOAD DATA LOCAL, How do I skip the first line?

3 Answers3

Linked

Related