Importing a CSV Into MySQL With a Header Row and Odd Quotes

A CSV with a header and a few awkward values, such as quotes inside quotes, a backslash in a Windows path and a field with a line break, is where MySQL's LOAD DATA stops being obvious. The statement understands quoted fields perfectly well, and it also carries a default from a different era, the backslash escape, that corrupts data quietly. This article loads one small file that contains each awkward case, shows what arrives in the table, and gives the clause that fixes each. The statement's basics are in using LOAD DATA INFILE, and the whole path is in the MySQL import guide. The clause syntax comes from the LOAD DATA reference, and the CSV rules it is being compared with are in RFC 4180.

The test file

Four records in a two column file. Record 1 has doubled quotes inside a quoted field, record 2 is plain, record 3 has a backslash path, and record 4 has a line break inside the quotes.

id,note
1,"He said ""hi"" and left"
2,"plain"
3,"C:\temp\new"
4,"two
lines"

The table is notes (id INT, note VARCHAR(100)).

The header

IGNORE 1 LINES tells MySQL to skip the first line of the file before it starts reading rows. It counts lines in the file, not records, which matters in a quirky case: if the header itself contains a quoted newline, one line is not the whole header. For an ordinary header, one is right. What the statement cannot do is look at the header. It does not compare names, it cannot tell you the columns are in a new order, and if the file has no header, IGNORE 1 LINES throws away your first record. I check the header in the shell first, with head -1 file.csv, and in a script compare it with the expected string before the load:

test "$(head -1 customers.csv)" = "id,name,email,signup_date,balance" || exit 1

If you do not want to depend on the column order of the file at all, read every field into a variable and assign by name in the SET clause, which makes the order of the column list in the statement the only place the order is written down, and the one that a reviewer can read.

Quotes and embedded newlines

With the usual clauses, the load handled the first and last records without help:

mysql -h mysql -uroot --local-infile=1 --raw lab <<'SQL'
LOAD DATA LOCAL INFILE 'quotes.csv' INTO TABLE notes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, REPLACE(note, CHAR(10), '<LF>') AS note, HEX(LEFT(note, 4)) AS first_bytes FROM notes ORDER BY id;
SQL

id	note	first_bytes
1	He said "hi" and left	48652073
2	plain	706C6169
3	C:	emp<LF>ew	433A0965
4	two<LF>lines	74776F0A

Record 1 is He said "hi" and left: the doubled quotes inside the enclosed field were read as one literal quote. Record 4 is two<LF>lines, which I made visible by replacing the line feed with a marker in the query. MySQL read the quoted newline as part of the value and not as the end of the record. The first_bytes column shows the stored bytes, so 48652073 is He s. These two cases pass on any CSV reader that implements the quoting rules, and it is good to see MySQL does.

The backslash

Record 3 is where it goes wrong. The value in the file is C:\temp\new, and the table shows C:, then a tab, emp, a line feed marker and ew. The first four stored bytes are 433A0965: C, :, and 09, a tab. The \t in the path was read as an escape sequence for a tab, and \n as one for a line feed. This is the default ESCAPED BY '\\', and it is not a bug: it is the format LOAD DATA was designed to read, the format MySQL's own SELECT ... INTO OUTFILE writes by default. The quoting article shows the same file loading correctly in PostgreSQL and Python.

When your values contain backslashes, which means Windows paths, regular expressions, LaTeX and many JSON fragments, set the escape to the empty string:

mysql -h mysql -uroot --local-infile=1 --raw lab <<'SQL'
TRUNCATE notes;
LOAD DATA LOCAL INFILE 'quotes.csv' INTO TABLE notes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\n' IGNORE 1 LINES;
SELECT id, REPLACE(note, CHAR(10), '<LF>') AS note FROM notes ORDER BY id;
SQL

id	note
1	He said "hi" and left
2	plain
3	C:\temp\new
4	two<LF>lines

With ESCAPED BY '', record 3 is C:\temp\new, as in the file, and the doubled quotes and the quoted newline are unchanged. Turning escapes off also turns off \N as the NULL marker, so a file that uses \N needs the other setting. The two conventions do not mix in one statement.

Windows line endings

A CSV from a Windows tool ends lines with a carriage return and a line feed. MySQL's default line terminator is \n, so each last field keeps a \r, which the line endings article measured as a four byte Ana that does not equal 'Ana'. The clause is LINES TERMINATED BY '\r\n':

mysql -h mysql -uroot --local-infile=1 --raw lab <<'SQL'
TRUNCATE notes;
LOAD DATA LOCAL INFILE 'crlf-quotes.csv' INTO TABLE notes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
SELECT id, note, LENGTH(note) AS len FROM notes ORDER BY id;
SQL

id	note	len
1	He said "hi"	12
2	x	1

The file has CRLF endings and a doubled quote. The note for record 1 loaded as He said "hi" with a length of 12, and record 2 as x with a length of 1. If the length had been 13 or 2, a \r would have come along. Check the length of a value from the last column whenever you load a file from a Windows system. If a file is a mixture of both, normalize it with a CSV aware tool first, since no single LINES TERMINATED BY value fits.

Loading only some columns

The same header can be used with a column list to drop fields. Fields you do not need go to a variable that the statement never uses:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
TRUNCATE notes;
LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE notes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (id, @name, @e, @d, @b) SET note = @name;
SELECT * FROM notes ORDER BY id;
SQL

id	note
1	Ana Silva
2	Smith, Jane
3	Bo Chen
4	Cy Dubois

This loaded the five column customers file into the two column notes table, with the name in the note column and the other three fields thrown away into @e, @d and @b. The column list controls what is read, and there is no way to name file columns by their header names, only by position. Mapping CSV columns to table columns takes this further, with a measured 2,000,000 row example.

How to tell it worked

The summary says Records: n. For a file with quoted newlines, the number of records is less than the number of lines, so count with a CSV reader, for example qsv count, and not wc -l. Select the awkward rows by id and read them, including the length. Then check SHOW WARNINGS, which should be empty. If you see a Row 1 was truncated; it contained more data than there were input columns warning, the delimiter or the quotes are wrong and the fields are being counted differently from what you think.

When the file is more than LOAD DATA can interpret

Three cases need another tool. A file whose quoting is inconsistent, with some fields quoted by one rule and some by another, will load wrongly with any setting, and the fix is upstream or in a repair script. A file with a multi-line header, such as a title row above the column names, needs IGNORE n LINES with the right n, and a file with repeated headers needs filtering. And a file in an encoding other than the connection's needs CHARACTER SET in the statement, as the garbled text guide explains. If any of these applies and the file is not trivially small, load it into an all text staging table first, where nothing is converted, and clean it with SQL.