MySQL Cookbook, 2nd Edition

Specifying the Structure of the Datafile

Problem

You have a data file that’s not in LOAD DATA’s default format.

Solution

Use FIELDS and LINES clauses to tell LOAD DATA how to interpret the file.

Discussion

By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeed (newline) characters and that data values within a line are separated by tab characters. The following statement does not specify anything about the format of the datafile, so MySQL assumes the default format:

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

Two LOAD DATA clauses provide explicit information about the datafile format. A FIELDS clause describes the characteristics of fields within a line, and a LINES clause specifies the line-ending sequence. The following LOAD DATA statement indicates that the input file contains data values separated by colons and lines terminated by carriage returns:

mysql>LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY '\r';

Each clause follows the table name. If both are present, the FIELDS clause must precede the LINES clause. The line and field termination indicators can contain multiple characters. For example, \r\n indicates that lines are terminated by carriage return/linefeed pairs.

The LINES clause also has a STARTING BY subclause. It specifies the sequence to be stripped from each input record. Like TERMINATED BY, the sequence can have multiple characters. If TERMINATED BY and STARTING ...