Can LOAD DATA INFILE Convert YYYYMMDD Dates to YYYY-MM-DD During Import?

Can LOAD DATA INFILE Easily Convert YYYYMMDD to YYYY-MM-DD?

Importing data with dates in the YYYYMMDD format can be a challenge. However, you can easily convert these dates to the more standard YYYY-MM-DD format using the LOAD DATA INFILE command in MySQL.

Solution:

The following LOAD DATA INFILE statement combines the data loading and date conversion into a single step:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
FIELDS TERMINATED BY ','
(column1, @var1, column3, ...)
SET column2 = STR_TO_DATE(@var1,'%Y%m%d')

In this command:

• The file.txt parameter specifies the input data file.
• The t1 table is the target for the imported data.
• The fields within the data file are separated by commas.
• The @var1 placeholder represents the YYYYMMDD date field.
• The STR_TO_DATE function converts the date in @var1 from %Y%m%d format to %Y-%m-%d format, storing the result in column2 of the target table.

By using this command, you can import and convert data in one go, eliminating the need for additional scripts or data transformations.

The above is the detailed content of Can LOAD DATA INFILE Convert YYYYMMDD Dates to YYYY-MM-DD During Import?. For more information, please follow other related articles on the PHP Chinese website!