MySQL Cookbook

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, you can skip it like this:

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

As of MySQL 4.0.2, mysqlimport supports an --ignore-lines=n option that has the same effect.

IGNORE is often useful with files generated by external sources. For example, FileMaker Pro can export data in what it calls merge format, which is essentially CSV format with an initial line of column labels. The following statement would be appropriate for skipping the labels in a merge file created by FileMaker Pro under Mac OS 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;

Note that importing a FileMaker Pro file often is not actually this easy. For example, if it contains dates, they may not be in a format that MySQL likes. You’ll need to preprocess your file first or postprocess it after loading it. (See Recipe 10.41.)