2

During the import from a CSV file created from an MS Access table into a MySQL table it converts all date fields in the csv into 0000-00-00. I use the following Load Data command for the import.

LOAD DATA LOCAL INFILE 'C:\\..\\tableName.txt'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Here's an example line from the CSV file im trying to import.

1,1,5/30/1934 0:00:00,"H","Vancouver, B.C."....

I tried the solution from "Dates changed to 0000-00-00" but it didn't work.

1

You can import your .csv file and replace after:

  • Make your date column new_date_column VARCHAR(10).

  • Import the file.

  • UPDATE with REPLACE.

UPDATE db1.table1 SET new_date_column = STR_TO_DATE(REPLACE(new_date_column,'/','.'),GET_FORMAT(DATE,'EUR'));

  • Change the datatype of new_date_column.

ALTER TABLE db1.table1 MODIFY new_date_column new_date_column DATE;

This will convert your new_date_column into XXXX-XX-XX date format.

1

Example Database table column names

database_column_1 database_column_2 database_date_column_name

Example CSV data fields

fname  lname hireDate
john  smith  09/09/2015

LOAD DATA LOCAL INFILE 'C:\/Documents and Settings/Admin/My Documents/datafile.csv' INTO TABLE data_import 
fields terminated by ','
lines terminated by '\r\n'
ignore 1 lines
(database_column_1, database_column_2, @temporary_date_variable)
SET database_date_column = STR_TO_DATE(@temporary_date_variable, '%m/%d/%Y')

In the above example you import the local csv data file and assign each data column from the CSV to the matching database column, when you get to the date column you will assign it to a temporary variable (you chose any name for the temporary variable just use the @ symbol at the front of it), then at the end use the SET command to set the database date column equal to the converted CSV value. The STR_TO_DATE function will use the temporary variable as the first parameter and then the format that the CSV date is in for the second parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.