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.

Croup

My answer was previously showing as accepted, but isn't any more. Did it not work, or was it a gremlin that changed its mind? – Alfeus

@Cez I think I may have accidentally double clicked it and made it unselected. lol. There you go – Croup

Much obliged. Glad that it solved the problem – Alfeus

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)

Alfeus

@amosrivera: I've added a link to the LOAD DATA syntax – Alfeus

Did I imagine it, or was this accepted and now showing as not accepted?? – Alfeus

@amosrivera: Thanks for preserving my sanity! – Alfeus

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;

Fredenburg

Answer 3

My suggestion would be to insert the file into a temporary holding table where the date column is a character datatype. Then write a query with theSTR_TO_DATE conversion to move the data from the holding table to your final destination.

Matriarch

Answer 4

1. Convert field that you are using for the date to varchar type so it will play friendly with any format

2. Import CSV

3. Convert the dates to a valid mysql date format using something like:

    UPDATE table SET field = STR_TO_DATE(field, '%c/%e/%Y %H:%i');

1. Then revert field type to date

Fehr

Answer 5

Use a function to convert the format as needed.

I'm not an expert on MySQL, but http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date looks promising.

If you can't do that in the load command directly, you may try creating a table that allows you to load all the values as VARCHAR and then to do an insert into your game table with a select statement with the appropriate conversion instead.

Robynroc

Answer 6

If you file is not too big, you can use the Excel function TEXT. If, for example, your date is in cell A2, then the formula in a temporary column next to it would be =TEXT(A2,"yyyy-mm-dd hh:mm:ss"). This will do it and then you can paste the values of the formula's result back into the column and then delete the temporary column.

Aconite