MySQL Date issue when import data using LOCAL INFILE

Question

I had data with the date column showing 5 numbers like 43390.

The data looks like this before conversion on Excel:

date
43390
43599

I converted it to date type in excel and import it using local infile to MySQL Workbench.

Data on Excel after conversion: |date| |:---- |2018/10/17| |2019/05/14|

However, I have been experiencing multiple issues on MySQL that would like to have your help on.

Firstly, when I created a table on MySQL as shown below:

CREATE TABLE df
(
dates DATE,
store_num INT,
loyalty_card_num INT,
txin_id INT,
prod_num INT,
product_name VARCHAR(250),
product_qty INT,
total_sales FLOAT
);

Then imported using code below:

LOAD DATA LOCAL INFILE 'file_location/data.csv' 
INTO TABLE df
FIELDS TERMINATED BY ',' 
ENCLOSED BY "'" 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(dates, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales);

I got 0000-00-00 on my dates column.

If I changed the dates column type to VARCHAR(20) or INT it only shows year like 2018.

I also tried using

(@dates, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales);
set dates = date_format(@dates, "%Y-%m-%d)

but returned null values.

I just want it to show the proper date format like 2018-01-01 but really struggle to get the result.

Thank you for your help, let me know if I explained anything unclear.

You don't want DATE_FORMAT, which converts from dates to text. You want STR_TO_DATE, which goes in the opposite direction, converting text to dates. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 5, 2021 at 9:09

Tim Biegeleisen · Accepted Answer · 2021-11-05 09:08:53Z

2

You may invoke STR_TO_DATE here and convert the text strings in the input file to proper dates as part of the load data process:

LOAD DATA LOCAL INFILE 'file_location/data.csv' 
INTO TABLE df
FIELDS TERMINATED BY ',' 
ENCLOSED BY "'" 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@var1, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales)
SET dates = STR_TO_DATE(@var1, '%Y/%m/%d');

answered Nov 5, 2021 at 9:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Data Beginner Over a year ago

Thanks for the response. I tried your method with dates type as VARCHAR(20), it shows null value on the dates column. Should I try a different data type?

Tim Biegeleisen Over a year ago

@Peter The column type should be date. You should stick with your current table definition.

Collectives™ on Stack Overflow

MySQL Date issue when import data using LOCAL INFILE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related