Changing CSV field when importing to mysql

Question

I am trying to import a CSV file into mysql database.

The CSV file contains, but is not limited to, dates in the following format:

"2010-04-31 17:43:12"

My first approach was to use the following .sql script:

USE test;
LOAD DATA INFILE '/tmp/test.cvs'
replace INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(...,mydate,...);

which does not work because double quotes make the "2010-04-31 17:43:12" field a string. So I realized that I can convert it to DATETIME format using

select STR_TO_DATE("2010-04-31 17:43:12",'(%Y-%c-%e %H:%i:%S)') AS NewDateTime

This query works fine, but I was wondering if there is a way to convert the string on the fly when importing. Some of the following:

...
LINES TERMINATED BY '\n'
(...,STR_TO_DATE(mydate,'(%Y-%c-%e %H:%i:%S)') AS NewDateTime,...);

Answer 1

, SET STR_TO_DATE:

(@date,column2,column3,column4,column5,column6,)
SET mydate = STR_TO_DATE(@date, '%Y-%c-%e %H:%i:%S')

:

• http://forums.mysql.com/read.php?10,136269,136269#msg-136269