How to convert date in .csv file into SQL format before mass insertion

Question

I have a csv file with a couple thousand game dates in it, but they are all in the MM/DD/YYYY format

2/27/2011,3:05 PM,26,14

(26 and 14 are team id #s), and trying to put them into SQL like that just results in 0000-00-00 being put into the date field of my table. This is the command I tried using:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`date`, `time`, `awayteam_id`, `hometeam_id`);

but again, it wouldn't do the dates right. Is there a way I can have it convert the date as it tries to insert it? I found another SO question similar to this, but I couldn't get it to work.

Answer 1

Have you tried the following:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@DATE_STR, `time`, `awayteam_id`, `hometeam_id`)
SET `date` = STR_TO_DATE(@DATE_STR, '%c/%e/%Y');

For more information, the documentation has details about the use of user variables with LOAD DATA (about half-way down - search for "User variables in the SET clause" in the page)

Answer 2

You can use variables to load the data from the csv into and run functions on them before inserting, like:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(@datevar, @timevar, awayteam_id, hometeam_id)
SET date = STR_TO_DATE(@datevar, '%m/%d/%Y'),
SET time = etc etc etc;