CODEWITHSUNDEEP
mysqlcsv
Akshay Jagadale
Akshay Jagadale

Reputation: 151

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

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

Upvotes: 0

Views: 1568

Answers (1)

Sevle
Sevle

Reputation: 3119

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).

Upvotes: 0

Related Questions