MySQL Cookbook, 2nd Edition

Importing CSV Files

Problem

You need to load a file that is in CSV format.

Solution

Add the appropriate format-specifier clauses to your LOAD DATA statement.

Discussion

Data files in CSV format contain values that are delimited by commas rather than tabs and that may be quoted with double-quote characters. For example, a CSV file mytbl.txt containing lines that end with carriage return/linefeed pairs can be loaded into mytbl using LOAD DATA:

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

Or like this using mysqlimport:

%mysqlimport --local --lines-terminated-by="\r\n" \
               --fields-terminated-by="," --fields-enclosed-by="\"" \
               cookbook mytbl.txt