Error 1265 Data truncated when Importing with LOAD DATA INFILE-CodePudding

Using MySQL workbench Version 8.0.26 on Windows 10, on a local connection.

Ok, so I'm importing data from a .csv using LOAD DATA INFILE, and it gets stuck at the first row (error 1265, column "delivery_cost" truncated etc.) and I do not understand why. Can somebody explain how this works?

LOADing code:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/receipts.csv'
INTO TABLE receipts
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(
order_id,
order_date,
customer_id,
delivery_date,
total_order,
delivery_fee,
payment_method, 
delivery_cost
)

This is how my column is structured:

delivery_cost DOUBLE (10,2) DEFAULT 0

.csv looks like this (delivery_cost is the last one, header not included to save space):

1,2021-10-07,771,2021-10-08,150,6,1,6.0
2,2021-10-07,865,2021-10-08,29300,0,2,27.0
3,2021-10-07,823,2021-10-09,14200,0,4,13.0

The data type & size looks fine. I even replaced the NULL default with 0. Changing the number of digits from DOUBLE makes no difference [ DOUBLE(10,2) to DOUBLE(10,1) or (10.3) ].

It works when I'm using SET = FLOOR(@delivery_cost) to get only the whole number. It works when I'm using SET delivery_cost = @delivery_cost -0.5 (for some reason), and funny enough it works when I'm using SET delivery_cost = @delivery_cost -0.5 0.5 (so I get the exact same number that I want to import). Actually it works even with SET delivery_cost = @delivery_cost 0.

I am extremely confused about why is this. I guess it's no big deal because I found a workaround, but I want to understand the root cause. Note: This is my first StackOverflow post and I'm new to programming (SQL is my first language). Ty <3

CodePudding user response：

Solved in the comment by wchiquito. Needs "LINES TERMINATED BY '\r\n'"