How to Import a CSV with Non-Standard Date Format into MySQL?

Importing a CSV to MySQL with Non-Standard Date Format

When attempting to import a CSV file into MySQL, you may encounter date formatting discrepancies between the CSV file and the desired DATE datatype in the database. One common issue is when dates in the CSV are formatted in a non-standard format, such as "31-Jan-2011."

Resolving the Date Format Discrepancy

To address this issue, you can utilize MySQL's STR_TO_DATE() function during the import process to convert the non-standard date format to the desired DATE datatype format of "2011-01-31." Here's an example that demonstrates how to achieve this conversion:

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

In this example, the @date_time_variable reads one of the fields from the CSV file. The STR_TO_DATE() function is used to convert the date string stored in @date_time_variable to the desired DATE datatype format, specifying the input format as "%d-%b-%Y" (day-month-year). This conversion ensures that the date is stored in the correct format in the date_time_column of the table_name table.

By implementing this technique, you can successfully import your CSV data into MySQL while maintaining the desired DATE datatype and resolving any date formatting discrepancies.

The above is the detailed content of How to Import a CSV with Non-Standard Date Format into MySQL?. For more information, please follow other related articles on the PHP Chinese website!