b.i b.i - 1 year ago 159
MySQL Question

Date imported from csv into mysql as 0000-00-00

I have some data saved as txt file. I am saving the txt file as csv in order to import it into a database using my sql workbench.
what I am doing is the following:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' lines terminated by '\n';

But one of my column is a date, and it is imported as 0000-00-00

How to import it in a good way ?
Here is what my csv contains:

id task hoursWorked begindate enddate
0 task1 15 11/17/2012 11/18/2012
1 task2 20 11/18/2012 11/20/2012
2 task3 20 12/4/2012 12/5/2013
3 task4 22 1/5/2013 1/7/2013

Answer Source

Please have a try with this one:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' 
INTO TABLE mytable 
(id, task, hoursWorked, @var1, @var2) 
SET begindate = STR_TO_DATE(@var1, '%m/%d/%Y'),     
enddate = STR_TO_DATE(@var2, '%m/%d/%Y');

For more info see LOAD DATA and STR_TO_DATE

Note: I deleted the FIELDS TERMINATED BY ',' ENCLOSED BY '"' part, cause I neither see , nor " in your CSV. But if it works fine for you the way it is, feel free to revert :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download