10.11 Skipping Datafile Lines
10.11.1 Problem
You want LOAD DATA to skip over
the first line or lines of your datafile before starting to load
records.
10.11.2 Solution
Tell LOAD DATA how many lines
to ignore.
10.11.3 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.)
| 
No comments:
Post a Comment