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;
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.