Load data local infile with double quotes and commas in fields

I'm trying to import data into a table. Currently I am trying

LOAD DATA local INFILE "C:/PRINT DAILY DOC_CommaDelimited.txt"
into table daily_doc_report_full
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 5 LINES;

The import data looks like this

"   1","SALES DEPT SALES             ","    0"," -285,723","

When it is importing the commas in the 4th field are messing up how the data is being imported.

The data should look like this

| Line| Description      | today | Month_to_date|
|    1| Sales Dept Sales |   0.00|   -285,723.00|

Instead the data is truncated and is displaying

| Line| Description      | today | Month_to_date|
|    1| Sales Dept Sales |   0.00|       -285.00|

I've tried various changes to the load data local infile query, like removing the OPTIONALLY from the ENCLOSED BY '"' but I just can't get it to work as intended. I'm using MYSQL 8.0

Solution

MySQL doesn't understand numbers formatted with comma for thousands separators. If you try to cast a string containing commas to a numeric, it ignores any characters from the comma to the end.

This has nothing to do with LOAD DATA INFILE or the enclosing quotes. We can demonstrate the same problem with a simple CAST() expression:

mysql> select cast('-285,723' as decimal);
+-----------------------------+
| cast('-285,723' as decimal) |
+-----------------------------+
|                        -285 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('-285723' as decimal);
+----------------------------+
| cast('-285723' as decimal) |
+----------------------------+
|                    -285723 |
+----------------------------+

You can work around this with LOAD DATA INFILE by first copying the string to a user variable, then removing the commas from the user variable.

I tested this with MySQL 8.3 (it should work the same with any version of MySQL):

mysql> LOAD DATA local INFILE 'c.csv' into table mytable 
  fields terminated by ',' 
  OPTIONALLY ENCLOSED BY '"' 
  (line, description, @today, @month_to_date) 
  set today = replace(@today, ',', ''), 
      month_to_date = replace(@month_to_date, ',', '');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from mytable;
+------+-------------------------------+-------+---------------+
| line | description                   | today | month_to_date |
+------+-------------------------------+-------+---------------+
|    1 | SALES DEPT SALES              |  0.00 |    -285723.00 |
+------+-------------------------------+-------+---------------+