How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

Question

I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from an CSV file into an existing database table.

Here is an example SQL statement:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, dateOfBirth)

The third column in the CSV that maps to the dateOfBirth field currently has the date in the following format:

14-Feb-10

How can I modify the above SQL statement to format the date into MySQL's date format i.e. 2010-02-14?

I know how to convert a string date when using normal INSERT syntax using:

STR_TO_DATE('14-Feb-10', '%d-%M-%y')

Answer 1

You need to use the SET clause, along with a variable to reference the contents of the row at that column. In your column list, you assign your date column to a variable name. You can then use it in your SET statement. (Note, I haven't got MySQL in front of me to test this on.)

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, @var1)
set dateOfBirth = STR_TO_DATE(@var1, '%d-%M-%y')

See examples a way down the page at: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/load-data.html (Not sure why this page seems to differ from the main documentation in that it actually contains an example of SET usage.)