Recipe 10.9. Skipping Datafile Lines

Problem

You want LOAD DATA to skip over the first line or lines of your datafile before starting to load records.

Solution

Tell LOAD DATA how many lines to ignore.

Discussion

To skip over the first n lines of a datafile, add an IGNORE n LINES clause to the LOAD DATA statement. For example, if a tab-delimited file begins with a line consisting of column headers, skip that line like this:

mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl IGNORE 1 LINES;             

mysqlimport supports an --ignore-lines= n option that has the same effect.

IGNORE is often useful with files generated by external sources. For example, a program might export data in CSV format with an initial line of column labels. The following statement would be appropriate for skipping the labels in such a file that has carriage return line endings:

mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl     -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'     -> LINES TERMINATED BY '\r'     -> IGNORE 1 LINES;