Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

LOAD DATA turns empty CSV fields into 0 or fails with Data truncated

ErrorERROR 1265 (01000): Data truncated for column 'price' at row 1

Short answer

LOAD DATA reads \N as NULL, not an empty field. An empty field in a numeric column becomes 0, or raises an error in strict mode. Write \N in the file, or load the column into a user variable and set NULLIF(@var, '') so empty values become NULL. Also match the line ending and skip the header.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the load with LOCAL or IGNORE, then read the warnings. They give the row and column that failed.

    SHOW WARNINGS;
  2. 2Match what you see, then jump to its fix

    1. 1Numeric columns hold 0, or strict mode stops the load on the first empty cell.Empty field is not the NULL markerFix
    2. 2The text NULL appears as a string, or values do not line up with the columns.The file has the word NULL or Windows line endingsFix
    3. 3SHOW WARNINGS lists a value like 'x' or 'N/A' for a numeric column at a given row.A non-numeric value is read into a number columnFix

What does the error look like?

  • ERROR 1265 (01000): Data truncated for column 'price' at row 1
  • Incorrect integer value: '' for column 'qty' at row 1
  • Empty cells land as 0 instead of NULL

Cause 1 of 3

Empty field is not the NULL marker

1Check

The manual says a field value of \N is read as NULL (with the default ESCAPED BY backslash), and that an empty field in a numeric column is set to 0. In strict mode the load stops with an error. When the load uses LOCAL or IGNORE, errors become warnings and the load continues.

SELECT COUNT(*) FROM t1 WHERE price = 0;

2Fix

Read the column into a user variable, then turn the empty string into NULL in the SET clause.

LOAD DATA LOCAL INFILE '/home/me/data.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, @price, qty)
SET price = NULLIF(@price, '');

3Verify

Empty cells are now NULL, not 0.

SELECT COUNT(*) FROM t1 WHERE price IS NULL;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The file has the word NULL or Windows line endings

1Check

With FIELDS ENCLOSED BY, a bare NULL word is read as NULL but a quoted "NULL" is the string. The manual's CSV example for Windows files uses LINES TERMINATED BY '\r\n'.

head -n 3 data.csv | od -c | head

2Fix

Choose the terminators that match the file, and skip the header line.

LOAD DATA LOCAL INFILE '/home/me/data.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

3Verify

The warning count drops to zero.

SHOW WARNINGS;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A non-numeric value is read into a number column

1Check

In strict mode an invalid value such as 'x' for a numeric column is an error. In non-strict mode, or with LOCAL or IGNORE, it is converted to 0 with a warning.

SHOW WARNINGS;

2Fix

Read the column into a variable and clean it in SET, or fix the file. Use LOCAL or IGNORE only when you really want bad rows to become warnings.

(name, @price, qty)
SET price = NULLIF(@price, 'N/A');

3Verify

Rerun the load and compare the row count with the file.

SELECT COUNT(*) FROM t1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Export data with \N for NULL, or load every nullable column through a variable and NULLIF.
  • Test a load on a copy of the table and read SHOW WARNINGS before the real run.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.