I have a csv (of Control M batch data) that I need to load into a MySQL table, but it has a selection of timestamps and dates that I need to format, so that they can be accepted by the MySQL TIMESTAMP and DATA data types.
20141031,20141031,MY_GROUP,MY_JOB,01/11/2014 00:30,01/11/2014 00:30,Ended OK
Here’s my code with the appropriate formatting:
LOAD DATA INFILE 'D:/Build/MySQLDump/ControlM stats for Kieran.csv' INTO TABLE stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (title1,title2,report_gen,title3,title4,title5,title6,title7,title8,title9,title10,title11,title12,title13,@order_date,title15,job_name,@start_time,@end_time,job_status,title16) SET order_date = STR_TO_DATE(@order_date, '%Y%m%d'), start_time = STR_TO_DATE(@start_time, '%d/%m/%Y %H:%i'), end_time = STR_TO_DATE(@end_time, '%d/%m/%Y %H:%i');
I’ve used the @variable placeholder so I can reference the column name in the SET command.
‘%Y%m%d’ : will format 20141031 correctly.
‘%d/%m/%Y %H:%i’ : will format 01/11/2014 00:30 correctly. Note the uppper “H” for 24Hour Format.