Import csv mysql with multiple custom date formats

Question

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?

I would import the data as a string (varchar) and convert it to the desired format after you've imported it — Barranka
– Barranka, Commented Jan 15, 2015 at 1:47

Navik Hiralal · Accepted Answer · 2015-01-15 04:35:21Z

3

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;

answered Jan 15, 2015 at 4:35

Navik Hiralal

7871 gold badge6 silver badges17 bronze badges

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

Comments

panibrat · Accepted Answer · 2018-12-28 20:59:16Z

1

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.

answered Dec 28, 2018 at 20:59

panibrat

113 bronze badges

Collectives™ on Stack Overflow

Import csv mysql with multiple custom date formats

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related