date conversion in csv to mysql db format

Question

Hi,

I am exporting a csv file in to mysql db using load data infile syntax. the date in csv is in 2009/10/31 7:8:57.0 format. Is there any way to convert this while loading to something like 2009-10-31 07:08:57 ?

Answer 1

(usual caveats apply here) A regular expression might be what you need. Substitute / with - and remove the trailing .0. I am surprised, though, that mysql can't handle dates like the one you provided. See for example the mySql manual. Have you tried feeding it to mysql and seeing what happens?

Answer 2

Execute TO_CHAR(TO_DATE(datefromcsv, 'YYYY/MM/DD HH:MI:SS.FF'), 'YYYY-MM-DD HH:MI:SS') when you are doing the INSERT into the db.

Answer 3

2009/10/31 7:8:57.0 is an unusual date format. You can try to import data via script (a php script for example):

• transform the date
• generate an insert query for each row
• execute the query

Alternatively, if its OK for you to edit the CSV data, I suggest that you use a formula or a date format to change the date format and export it into a new CSV file.

In excel, this formula should do the trick:

A1: 2009/10/31 7:8:57.0
B1: =SUBSTITUTE(A1,"/","-")
C1: =IF(ISERR(FIND(".",B1)),B1,LEFT(B1,FIND(".",B1)-1))

• A1 contains the date as a string
• B1 contains a partially cleaned-up date
• C1 contains the cleaned-up date