Search code examples
mysqlsqlcsvdatetimeload-data-infile

MySQL 8.0: Error when inserting date data

I have a tab separated file as follows:

enter image description here

When i try to import the file as follows in MySql:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer.csv'
INTO TABLE customers
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

I get the following error:

Incorrect date value: '02/15/2008' for column 'BI_signup_date' at row 1

How do I fix this?

Solution

  • The format of dates in your file do not match the format expected by MySQL. Also you have some non-date values in that column ('not BI'). A solution is to preprocess your input to transform it to the relevant format.

    LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer.csv'
INTO TABLE customers
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(customer_id, @BI_signup_date, email_address)
SET BI_signup_date = CASE
    WHEN @BI_signup_date = 'not BI' THEN NULL
    ELSE STR_TO_DATE(@BI_signup_date, '%m/%d/%Y')
END;

    This will turn value 'not BI' to NULL(since obviously this cannot be converted to a date), and convert other values to MySQL date format. Other strategies are possible, like checking the format of the value against a regex. You can modify the logic as needed.

    Reference: Input preprocessing in MySQL LOAD DATA syntax:

    Each col_name_or_user_var value is either a column name or a user variable. With user variables, the SET clause enables you to perform preprocessing transformations on their values before assigning the result to columns.