Dfyjkt

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

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

1. Parse the line to get the string "7,344"
   


2. Strip the enclosing ": 7,344
   


3. Store the string into the INT column. This requires converting that string to a number.


4. Conversion stops at the first non-numeric character, namely the comma.


5. 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, ',', '')