Import CSV to SQL - Date format issues: Zeroes

1
votes

I'm trying to import CSV data into MySQL database. I have four Date fields. when the import finishes, all my dates are zeroes: enter image description here.

I've set the date format to DATE in Excel prior to saving it as CSV. I've set the table field as DATE in MySQL.

I'm using phpMyAdmin to do the import.

1
mysqlsqlcsvimport
What input looks like? What command do you use tp import data? – Maciej Los
You have to show us a bit more... – Feign
Date format mismatch results in 000... have a look at format. – singhswat

1 Answers

1
votes

Mysql only excepts dates in YYYY-MM-DD format.

The Date format in excel is 06/09/2015. Thus, not in the format that mysql is expecting. You must change this to a format that is accepted in mysql otherwise all your date fields will appear as 0000-00-00.

In order to change the date format in excel: right click on the top cell. Choose format cells from the drop down list. change the local to something like 'Afrikans'. Choose the format that looks like 2001-03-14. Use the top cell to fill down. Then save the document.

Just a quick note: Excel sometimes tries to do too much and will revert this column back to a the English(U.S) default time zone. So, if you plan on doing more editing make sure that the column has not reverted back.

Here is a link to more string literals on dev.mysql.