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

Question

I have some data saved as a txt file. I save the txt file as csv to import it into the database using my SQL-Workbench. I do 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 columns 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 

Answer 1

Please try with this:

 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'); 

See LOAD DATA and STR_TO_DATE for more details.

Note. I deleted the FIELDS TERMINATED BY ',' ENCLOSED BY '"' , so I don’t see , and " in your CSV. But if it works great for you as it is, feel free to come back :)

Answer 2

Default Date Format: YYYY-MM-DD :

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

... therefore MySQL will not recognize things like 11/17/2012 as the correct date. In theory, you should be able to change the default format, but I'm not sure that this can be done in the session area, and I would not recommend changing it for the entire server. It is best to do the conversion. The trick is to insert the value into the variable, not the column.

In addition, there are two more problems:

• The CSV file contains a header line.
• Your fields are not shared.

Assuming your file uses tabs as delimiters, the complete command is:

 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 actually does not allow changing @@date_format in any way:

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

As explained by MySQL 5.6 manual :

This variable is not used. It has been deprecated since MySQL 5.6.7 and will be removed in a future release of MySQL.

In addition, in the "Date and Time Types" we can read:

MySQL retrieves values ​​for a given date or time type in a standard output format, but it tries to interpret various formats for the input values ​​that you supply (for example, when you specify a value for are assigned or matched to a date or time type). For a description of the allowed formats for date and time types, see Section 10.1.3, “Date and Time Literals” . You are expected to provide valid values. Unpredictable results may occur if you use values ​​in other formats.

Although MySQL tries to interpret values ​​in several formats, date details should always be specified in year-month-day order (for example, '98 -09-04 '), and not in month-day-year or day-month-year commonly used in other places (e.g. '09 -04-98 ', '04 -09-98').