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

Question

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 ? Edit 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

Volplane

You've omitted both field contents and column definition... Can you edit the question and provide them? – Kibitka

Can you provide a small extract of the CSV so we can see the date format and also the TYPE of the mysql field you want to store the date. – Africanize

Invalid or misformatted dates get imported as 0000-00-00. You could format your date to yyyy-mm-dd during LOAD DATA (can't help you with that option). Or you can modify your csv file to format your dates with regular expression. (There are lots of examples out there regarding formatting dates with regex.) – Bilious

Your fields are not TERMINATED BY ','... :-? – Kibitka

@ Álvaro G. Vicario :everything is working correctly except values in begindate and enddate – Volplane

Answer 1

Please have a try with this one:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' 
INTO TABLE mytable 
LINES TERMINATED BY '\n'
(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 :)

Worker

this means that I have to convert each value in begindate and enddate alone? but I have a hundred values! – Volplane

A hundred isn't much, and this shouldn't perform too bad. Please have a try. – Worker

Also I'm sure this is the best way to go (and the only one, if you don't want to mess in your CSV by hand). – Worker

+1 This is the correct answer and it was posted 15 minutes earlier than mine :) – Kibitka

I wrote the query above, but it gives me an error: ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LINEs' at line 1 – Volplane

@Volplane Sorry, try again please. The columns have to be specified after LINES TERMINATED BY.... – Worker

@tombom: begindate and enddate values are null – Volplane

It is working :) i have to add FIELDS TERMINATED BY ',' ENCLOSED BY '"' :) – Volplane

Answer 2

The default date format is YYYY-MM-DD:

mysql> SELECT @@date_format;
+---------------+
| @@date_format |
+---------------+
| %Y-%m-%d      |
+---------------+

... thus MySQL won't recognise stuff like 11/17/2012 as a proper date. In theory, you should be able to change the default format, but I'm not sure it can be done in session scope and I wouldn't recommend to change it for the whole server. It's better to make the transformation yourself. The trick is to insert the value into a variable rather than a column.

Additionally, there're two other issues:

• Your CSV file contains a header line.
• Your fields are not separated by ,.

Assuming your file uses tabs as separators, the complete command would be:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv'
INTO TABLE mytable
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, task, hoursWorked, @tmp_begindate, @tmp_enddate)
SET begindate = STR_TO_DATE(@tmp_begindate, '%m/%d/%Y'),
    enddate = STR_TO_DATE(@tmp_enddate, '%m/%d/%Y');

---

MySQL doesn't actually allow to change @@date_format anyway:

mysql> SET @@date_format='%d/%m/%Y';
ERROR 1238 (HY000): Variable 'date_format' is a read only variable

As the MySQL 5.6 manual explains:

This variable is unused. It is deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release.

Also, at Date and Time Types we can read:

MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see Section 10.1.3, “Date and Time Literals”. It is expected that you supply valid values. Unpredictable results may occur if you use values in other formats.

Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Greenway

Answer 3

If it didn't work, just add columns to your CVS for year, month and day and separate day, month and year of your date, and use the following:

set date_column = concat(@year , '-' , @month , '-' , @day)

Cryosurgery