How to Import CSV Data with Different Date Formats into MySQL?

Importing a CSV to MySQL with Different Date Format

When importing CSV files into MySQL, it is common to encounter date formats that differ from the desired format, such as '31-Jan-2011' instead of '2011-01-31'. To resolve this issue, MySQL offers a solution using the STR_TO_DATE() function during the data import process.

Solution

Instead of performing conversions in PHP and inserting into a separate table, the solution provided by the expert involves modifying the data during the import itself. The following code demonstrates how to achieve this:

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

In the code above, @date_time_variable represents a temporary variable that stores the string date in the CSV file ('31-Jan-2011' in this case). The STR_TO_DATE() function then converts this string into the desired format ('2011-01-31'). The result is assigned to the date_time_column column in the table.

For more detailed information on this approach, refer to the MySQL documentation on LOAD DATA INFILE Syntax.

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