CODEWITHSUNDEEP
mysqldatecsv
ChiMo
ChiMo

Reputation: 601

Import csv mysql with multiple custom date formats

I have to import a csv into a mysql database with a funny date format so I decided to use STR_TO_Date() to convert it, however it pulls an error on the second conversion when I try to put multiple conversions for the dates.

LOAD DATA LOCAL INFILE 'e:/the.csv' INTO TABLE data FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (ID, UID, Barcode, Name, @Start, @End, Seconds) SET Start = STR_TO_DATE(@Start, '%d/%c/%Y %H:%i') SET End = STR_TO_DATE(@End, '%d/%c/%Y %H:%i')

My database looks along the lines of:

ID  UID     Barcode Name        Start               End                 Seconds
29  12345   BBB6168 namehere    09/12/2014 18:00    09/12/2014 18:09    538
30  12345   AAA6168 namehere    10/12/2014 11:01    10/12/2014 11:02    4
31  AAAAA   ABC6084 namehere    10/12/2014 11:34    10/12/2014 11:34    5

How can I fix this?

Upvotes: 0

Views: 1004

Answers (2)

panibrat
panibrat

Reputation: 11

As per MySQL documentation for LOAD DATA INFILE the code would be the following:

LOAD DATA LOCAL INFILE 'e:/the.csv'
INTO TABLE data
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(ID, UID, Barcode, Name, @Start, @End, Seconds)
SET Start = STR_TO_DATE(@Start, '%d/%c/%Y %H:%i'),
    End = STR_TO_DATE(@End, '%d/%c/%Y %H:%i');

Bonus: MySQL STR_TO_DATE() format options.

Upvotes: 1

Navik Hiralal
Navik Hiralal

Reputation: 787

As Barranka mentioned, its probably best to import into a table of string (varchar) columns and then run an insert into your final table.

LOAD DATA LOCAL INFILE 'e:/the.csv'
INTO TABLE data_temp_strings
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(ID, UID, Barcode, Name, StartStr, EndStr, Seconds);


INSERT INTO data (ID,UID,Barcode,Name,Start,End,Seconds)
SELECT  ID,
        UID,
        Barcode,
        Name,
        STR_TO_DATE(StartStr, '%d/%c/%Y %H:%i'),
        STR_TO_DATE(EndStr, '%d/%c/%Y %H:%i'),
        Seconds
FROM   data_temp_strings;

Upvotes: 3

Related Questions