Mysql Load File Error loading inaccurate data

Question

Hi I have a csv dataset like

ukwn,34,2018-10-01,"757,271"

ukwn,3,2018-10-01,"7,342"

"hi",23,2018-10-01,"3,483,887"

i want to insert it in the database so i made the code:

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE app_spend_metric FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (col1,col2,col3,col4)

But i fails to insert the col4 (4th row) as there is ',' inside a "" like "7,345"

I tried then,

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE app_spend_metric FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col1,col2,col3,col4)

But this time it enters the partial data in col4 like out of "7,344" it only enters "7

Rick James Rick James · Accepted Answer · 2018-11-08T20:00:40

If col4 is numeric (eg, INT), then the problem is as follows:

Parse the line to get the string "7,344"
Strip the enclosing ": 7,344
Store the string into the INT column. This requires converting that string to a number.
Conversion stops at the first non-numeric character, namely the comma.
Result: The col4 is set to 7, and ,344 is tossed.

MySQL cannot deal with "thousands-separators" in numbers. But you could strip them:

LOAD ...
    (col1, col2, col3, @num)
    SET col4 = REPLACE(@num, ',', '')

Mysql Load File Error loading inaccurate data

1 Answers