How to Import CSV Dates with Alternate Formats into MySQL?

Importing a CSV to MySQL with Alternate Date Format

Importing a CSV file into MySQL can introduce challenges when the date formats differ between the CSV and the database. Encountering dates in the format 31-Jan-2011, it's necessary to convert them to the compatible format 2011-01-31 to store them in a DATE datatype.

Fortunately, MySQL provides a straightforward method to handle this conversion during the import process. Utilizing the LOAD DATA INFILE command, you can specify a date format transformation rule as follows:

LOAD DATA INFILE 'file_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(id, column2, column3, @date_time_variable) -- read one of the field to variable
SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y'); -- format this date-time variable

By incorporating the STR_TO_DATE() function, you can convert the date string using the provided format (%d-%b-%Y in this case) into a MySQL-compatible DATETIME format. The resulting data will be correctly stored in the specified date_time_column.

Refer to the LOAD DATA INFILE Syntax documentation for further information on utilizing this technique for CSV imports.

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