How to Import CSV Dates with Different Formats into MySQL?

Importing CSV to MySQL with a Different Date Format

Importing CSV data with date formats different from the desired format can be challenging.

One common issue is when the dates in the CSV file are in a format that MySQL does not natively recognize, such as 31-Jan-2011. To import such data, it's necessary to convert the date format during the import process.

SOLUTION:

MySQL provides a solution through the LOAD DATA INFILE statement with format string conversion options:

LOAD DATA INFILE 'file_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(id, column2, column3, @date_time_variable) 
SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y');

Breaking down the statement:

• @date_time_variable: This variable temporarily stores the value from the CSV file's date column.
• STR_TO_DATE: The STR_TO_DATE function converts a string to a DATETIME value using a specified format.
• %d-%b-%Y: This format string instructs MySQL to expect dates in the format 31-Jan-2011.

Procedure:

• Import the CSV file using the above LOAD DATA INFILE statement.
• The date_time_column field in the target table will store the converted date in the YYYY-MM-DD format.

Example:

LOAD DATA INFILE 'dates.csv'
INTO TABLE date_table
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(id, date_column, @date_time_variable)
SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y');

The above is the detailed content of How to Import CSV Dates with Different Formats into MySQL?. For more information, please follow other related articles on the PHP Chinese website!