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 '
'
 -> 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.)

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net