mysqlsqletlload-data-infile

How to load data of string of date in csv to mysql database?

Here is a glimpse of my .csv file:

Name,Start Date,End Date,Budget
Class 1,1/1/2020,31/1/2020,"USD10,000.00"
Class 2,1/2/2020,28/2/2020,"USD14,000.00"

The date format in csv is %e/%c/%Y.

I am trying to load this data to a table class, here is what I have done:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/class.csv' 
INTO TABLE class
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(class_name, @start_date, @end_date, @budget)
SET start_date = REPLACE(@start_date, '/', '-'),
end_date = REPLACE(@start_date, '/', '-'),
budget = REPLACE(@budget, 'USD', ''),
budget = REPLACE(@budget, ',', '');

But it returns

Error Code: 1292. 
Incorrect date value: '1-1-2021' for column 'start_date' at row 1

How to handle this?

Solution

  • Seems you need to use STR_TO_DATE() function such as

    SET start_date = STR_TO_DATE(@start_date, '%d/%m/%Y')

    as start_date is a DATE type column, but not an ordinary string. Presumably, the same logic should work also forend_date.