optionally enclosed not working, commas breaking upload

I have this table:

CREATE TABLE my_table(
    trans_id INT NOT NULL AUTO_INCREMENT,
    spend decimal(10,2),
    impressions int,
    PRIMARY KEY (trans_id)
);

Here is my load statement:

LOAD DATA LOCAL INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/my_file.csv' 
INTO TABLE my_table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(spend, impressions);

In spend I’ll have a decimal value with a comma like: 1193.72.

The spend field gets uploaded as 1. The impressions field that follows is correct and unaffected.

Example record: 1/19/2021 | 1,193.72 | 92780

Output: 1/19/2021 | 1 | 92780

I was under the impression including optionally enclosed by '"' would solve this, and for other text fields it seems to work as expected, but here it’s causing me problems.

Answer

MySQL doesn’t support input of numeric literals formatted with thousands separators.

The best solution would be to pre-process your input file before you try to use LOAD DATA INFILE. Otherwise you could do this workaround:

LOAD DATA LOCAL INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/my_file.csv' 
INTO TABLE my_table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@tmp_spend, impressions)
SET spend = REPLACE(@tmp_spend, ',', '');

This stores the spend value into a temporary variable, instead of directly into the column. A user-defined variable is always a string type. Then you can remove the commas from that string before assigning it to the numeric spend column.