LOAD DATA turns empty CSV fields into 0 or fails with Data truncated
ERROR 1265 (01000): Data truncated for column 'price' at row 1Short 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
Numeric columns hold 0, or strict mode stops the load on the first empty cell.Empty field is not the NULL markerFix - 2
The 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
SHOW 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
- 1
What does the error look like?
ERROR 1265 (01000): Data truncated for column 'price' at row 1Incorrect integer value: '' for column 'qty' at row 1Empty 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
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 | head2Fix
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses or trims a value: Data truncated for column
Data truncated for column 'status' at row 1
MySQL reports truncation when a value does not fit the column.
3 causes, each with a fixDatabases
LOAD DATA LOCAL INFILE is refused: loading local data is disabled
ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side
LOAD DATA LOCAL works only when both the server and the client allow it.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix