Why is my data being truncated? Warning | 1265 | Data truncated for a column
I tried adding NULL to both date_of_birth and date_of_death, but that didn't help. Why is the data being truncated? (especially since there is no data at the moment ...)
Also, I don't understand warning 1261 at all. Line 5 is no different from lines 1-4.
Thank!
The following script:
-- create the people table
DROP TABLE IF EXISTS people;
CREATE TABLE people (
full_name varchar (50) NOT NULL,
people_ID varchar (15) NOT NULL,
date_of_birth DATE,
date_of_death DATE,
PRIMARY KEY(people_ID))
ENGINE = INNODB;
LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Gives me the following errors:
Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 10
mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 5 |
| Warning | 1261 | Row 5 doesn't contain data for all columns |
+---------+------+----------------------------------------------------+
10 rows in set (0.00 sec)
mysql> select * from people;
+-------------------+-----------+---------------+---------------+
| full_name | people_ID | date_of_birth | date_of_death |
+-------------------+-----------+---------------+---------------+
| Harry Dunham | H_Dunham | 0000-00-00 | NULL |
| Julien Bryan | J_Bryan | 0000-00-00 | 0000-00-00 |
| Jules V.D. Bucher | J_Bucher | 0000-00-00 | 0000-00-00 |
| Miriam Bucher | M_Bucher | 0000-00-00 | 0000-00-00 |
| Paul Bowles | P_Bowles | 0000-00-00 | 0000-00-00 |
+-------------------+-----------+---------------+---------------+
5 rows in set (0.01 sec)
Here is my .csv:
FullName,People_ID,DOB,DOD
Jules V.D. Bucher,J_Bucher,,
Miriam Bucher,M_Bucher,,
Julien Bryan,J_Bryan,,
Paul Bowles,P_Bowles,,
Harry Dunham,H_Dunham,,
source to share
It might happen that Mysql is trying to force an empty string ""
in the date column, but the column only accepts formatted data DATE
, so it gets converted to the correct format. Try setting up your CSV so that one of the rows has "0000-00-00"
dates in the columns, not just ""
(space) and see if there are fewer warnings.
The warning 1261
looks more similar - perhaps harmless, but annoying quirks in the way Mysql handles CSV imports - and I wouldn't bother with that unless you see evidence that your imported data is incomplete.
source to share
Putting it all together, I clarified that your CSV file contains DOS-style line endings ( \r\n
), whereas you instructed MySQL to interpret the file using UNIX-style line endings ( \n
).
So those \r
characters are treated as data, \r
truncated to nonexistence during the string-to- conversion DATE
, and the final line does not really contain any data for that last column (because you don't write an empty string at the end of the CSV file). The way the last line is different.
You can actually fix this by using a more appropriate directiveLINES TERMINATED BY
:
LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
MySQL will then allow sequences to \r\n
be treated as line terminations.
You have another problem: you are telling MySQL that your fields are enclosed in "
, when they are not at all. You can remove the directive ENCLOSED BY
or precede itOPTIONALLY
:
If the input values are not required to be quoted, use OPTIONAL before the ENCLOSED BY keywords.
You should read the documentation for the technologies used.
source to share