Using LOAD DATA INFILE to Import a CSV Into MySQL Tables

LOAD DATA is the fastest way to get a file into MySQL and the statement with the most defaults that do not match a CSV. This article walks through it clause by clause on a four row file, and shows what each choice does to the data that lands in the table. The wider path, including the settings that stop a load from starting and the strictness rule that turns bad dates into zeros, is in the guide to importing a CSV file into MySQL.

What you need

A table and a file. The file has a header, a quoted value with a comma inside it, an empty email and an empty balance:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75

The table:

CREATE TABLE customers (
  id INT PRIMARY KEY, name VARCHAR(60), email VARCHAR(80),
  signup_date DATE, balance DECIMAL(10,2)
);

The statement

For a CSV, name the delimiter, the quote and the line ending, and skip the header. The reference documents each clause, and these are the clauses that matter:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, name, email, email IS NULL AS email_null, signup_date, balance FROM customers ORDER BY id;
SQL

Level	Code	Message
Warning	1366	Incorrect decimal value: '' for column 'balance' at row 3
id	name	email	email_null	signup_date	balance
1	Ana Silva	[email protected]	0	2025-01-15	120.50
2	Smith, Jane	[email protected]	0	2025-02-03	0.00
3	Bo Chen		0	2025-02-20	0.00
4	Cy Dubois	[email protected]	0	2025-03-01	15.75

FIELDS TERMINATED BY ',' sets the delimiter. OPTIONALLY ENCLOSED BY '"' says a field may be wrapped in double quotes, which is how Smith, Jane arrived as one value. The OPTIONALLY matters for output, since on input the quote is honored either way. LINES TERMINATED BY '\n' is the default and I write it so that the file's convention is on the page, and the line endings article shows what happens when it is wrong. IGNORE 1 LINES skips the header. The format rules behind the quoting are the ones in RFC 4180, and MySQL's variations on them are listed in the reference.

The first result is the warnings, since after a load SHOW WARNINGS is the second most important output. Here: Incorrect decimal value: '' for column 'balance' at row 3. The table shows what the warning means, and it is the central finding of this article: the empty email is the empty string, not NULL, because email_null is 0, and the empty balance is 0.00.

Why empty is not NULL

MySQL's LOAD DATA has its own rules for what a field's text becomes. Unquoted empty text in a string column is an empty string. In a numeric column it is zero, with a warning. In a date column it becomes a zero date, or an error on a server load. The only text that means NULL is \N, written exactly, because it is the output convention of SELECT ... INTO OUTFILE and of LOAD DATA itself. A CSV from a spreadsheet or a Python script does not use it.

So an empty cell in a CSV needs to be converted on the way in. The statement can do that, with a user variable standing in for the column and a SET clause computing it:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (id, name, @email, signup_date, @balance)
  SET email = NULLIF(@email, ''), balance = NULLIF(@balance, '');
SHOW WARNINGS;
SELECT id, email, email IS NULL AS email_null, balance, balance IS NULL AS balance_null FROM customers ORDER BY id;
SQL

id	email	email_null	balance	balance_null
1	[email protected]	0	120.50	0
2	[email protected]	0	0.00	0
3	NULL	1	NULL	1
4	[email protected]	0	15.75	0

(id, name, @email, signup_date, @balance) reads the third and fifth fields into variables and the others straight into their columns. SET email = NULLIF(@email, ''), balance = NULLIF(@balance, '') assigns the real columns. Row 3 now has NULL in both columns, email_null and balance_null are 1, and the warning list is empty. This pattern is also how you reformat dates, drop columns and add computed ones, and column mapping has the details. Whether NULL is what you want depends on the column, as in the NULL article for PostgreSQL, where the default is the other way around.

The defaults you do not want

Run the same statement with only the table name and file, as most snippets on the internet do, and MySQL treats the file as tab separated. I loaded the comma file with FIELDS TERMINATED BY ',' omitted in the hub's test, and the whole line went into the first column with the others NULL. The manual states the defaults: the same as writing FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'. The escape default is the one that bites in a CSV, because a backslash inside a value is read as an escape and C:\temp\new becomes a path with a tab and a newline in it, as the quoting article showed. When your data has backslashes, add ESCAPED BY ''.

Duplicates: stop, skip or replace

If the file contains an id that is already in the table, a server side LOAD DATA without options stops with ERROR 1062 Duplicate entry '1' for key 'customers.PRIMARY'. I hit that by accident while testing, because the table still held rows from the previous run. Two keywords change it. IGNORE skips the duplicate row and carries on, which is also what LOCAL does by default. REPLACE deletes the existing row and inserts the new one, which is an upsert with a catch: any column not in the file goes back to its default, and the row's auto increment id changes if there is one.

The place for the keyword is just before INTO TABLE: LOAD DATA LOCAL INFILE 'f.csv' REPLACE INTO TABLE customers .... Neither is a substitute for knowing whether your file may contain keys that exist. For a load that is meant to be idempotent, load into a staging table and use INSERT ... ON DUPLICATE KEY UPDATE, which lets you say which columns to overwrite.

LOCAL or not

LOAD DATA LOCAL INFILE reads the file on the client, and without LOCAL the server reads a file on its own filesystem. The server side form needs the FILE privilege and a path under secure_file_priv, and the local form needs local_infile on both sides. I ran the server form on a file placed in the allowed directory:

mysql -h mysql -uroot lab -e "TRUNCATE customers; LOAD DATA INFILE '/var/lib/mysql-files/customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (id, name, @e, signup_date, @b) SET email = NULLIF(@e,''), balance = NULLIF(@b,''); SELECT id, name, email FROM customers ORDER BY id" 2>&1

id	name	email
1	Ana Silva	[email protected]
2	Smith, Jane	[email protected]
3	Bo Chen	NULL
4	Cy Dubois	[email protected]

It loaded the four rows with the same mapping, NULL for Bo Chen's email. The other difference is strictness, which matters more than the location: a LOCAL load downgrades value errors to warnings, and the server side load keeps strict mode and fails. The hub article has the two results side by side, and the two error messages that stop a load from starting are in the articles on error 1290 and LOCAL being disabled.

Reading the result

The statement returns a summary line, which the mysql client prints interactively or with -vvv: Records: 4 Deleted: 0 Skipped: 0 Warnings: 1. In scripts, capture the warning count in the same session, with SELECT @@warning_count immediately after the load, as the reliable import script does. Compare Records with the number of data lines in the file, counted by a CSV aware tool, since wc -l miscounts quoted newlines. And query the columns that can go wrong: the date range, the largest number, the longest string.

What the statement cannot do

It cannot transform arbitrary text: the SET clause takes expressions, but a value that needs several steps of cleaning is easier in a staging table. It cannot select columns of the file by name, only by position. It cannot report per-row errors as errors in a local load, which is the strictness problem. It does not understand a header, so a file whose column order changed loads into the wrong columns without a check; compare the header yourself before loading. And it is not a generic COPY: there is no ON_ERROR option, no reject limit and no structured log of what it skipped.

For a few hundred rows, the import wizard in a GUI client is quicker to use, as the Workbench article describes. For files that need real cleaning, load everything into VARCHAR columns, which cannot clip or convert, and then do the conversion in SQL where you can see which rows fail.