Date columns (datatype -timestamp) not imported correctly from csv into MySql

Question

I am trying to import data from a csv file into MySQL using LOAD DATA LOCAL INFILE. All columns except the date columns which have timestamp as their datatype are imported correctly. I am getting the error 1265: data truncated for date column and it inserts 0000-00-00 00:00:00 for all values.This has been asked before but I did not find a perfect solution for this. I have also tried various solutions posted for this type of question but none have worked for me.

table create statement :

CREATE TABLE MySchema.response
(
`id` int,
`version` int,
`name` varchar(500), 
`date_created` timestamp,
`last_updated` timestamp, 
`count` int,
);

loading data into table:

LOAD DATA LOCAL INFILE 'C:/response.csv' 
INTO TABLE MySchema.response
FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' 
ignore 1 lines

Sample Data in CSV file

id version name date_created last_updated count
1, 0, xyz, 5/3/2013 1:18, 5/3/2013 1:18, 2
2, 0, abc, 5/3/2013 1:18, 5/3/2013 1:18, 1

Sevle · Accepted Answer · 2016-03-05 22:22:56Z

0

Date columns in your sample are not in MySQL's default format and thus are not identified as dates. You need to try something like the following, in order to state how the dates should be interpreted:

LOAD DATA LOCAL INFILE 'C:/response.csv' 
INTO TABLE MySchema.response
FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' 
IGNORE 1 lines
(id, version, name, @date_created, @last_updated, count)
SET date_created = STR_TO_DATE(@date_created, '%d/%c/%Y %k:%i'),
last_updated = STR_TO_DATE(@last_updated, '%d/%c/%Y %k:%i');

Check MySQL date format documentation for specifiers that suit your case (probably the ones I added in the sample above).

answered Mar 5, 2016 at 22:22

Sevle

3,1192 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akshay Jagadale Over a year ago

Only change I had to make was changing the place of month and days. In my csv file the format was mm-dd-YYYY. Fantastic Solution and thank you for the documentation on date formats as well

Collectives™ on Stack Overflow

Date columns (datatype -timestamp) not imported correctly from csv into MySql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related