Remove double quote within double quoted string using Replace command

Question

The source provide the few rows with additional double quote in the CSV file.

Want to replace the additional double quotes with pipe (|) (e.g., for "G" and "Junior", below) which will be reverted to double quote after processing of the data. Numerical fields are without double quote and some values are with double quote. Blank values are defined in double quote.

1,"abc","Class (""G"") as agreed , with new Value x",7004,"rec"
2,"prq","Promoting ""Junior"" to Senior, showing good results","",x

Result should be:

1,"abc","Class (|G|) as agreed , with new Value x",7004,rec
2,"prq","Promoting |Junior| to Senior, showing good results","",x

We have to process CSV files through ADF pipeline using Copy command. Each file contains 1 mill+ records. SFTP --> ADLS --> database. We need to fix this extra double quote issue while processing the files from ADLS to database.

We are not using JSON or databricks or python. it's just a simple file load into database.

Tried below solutions -- The below link replaces comma within double quote but tried to change it as per requirement but no luck,

("[^",]+)""([^"]+")

regex to remove comma between double quotes notepad++.

Thank you in advance.

Ok thanks, let me give a look if I can simulate the scenario — 70ny, Jun 23 '23 at 14:12
Hi, sachin. Just want to make sure you understand that `""G""` is the default CSV encoding for `"G"`; same for `""Junior""` which will probably be _read_ as `"Junior"` by whatever CSV processor/importer your using. Replacing them with something else just to revert that seems like a misstep here. — Zach Young, Jun 23 '23 at 18:45

aborruso · Answer 1 · 2023-06-24T07:43:04.700

You should use a CSV aware tool. One of this is Miller.

Running

mlr --csvlite -N put '$3=gsub($3,"\"\"","|")' input.csv

you get

1,"abc","Class (|G|) as agreed , with new Value x",7004,"rec"
2,"prq","Promoting |Junior| to Senior, showing good results","",x

Some notes:

-N to set that is a no headging CSV;
put is the verb to run cells functios;
gsub the function to run global search and replace;
$3 to apply it to the third field.

Your CSV has some unnecessary quotes. It would be better to have

1,abc,"Class (|G|) as agreed , with new Value x",7004,rec
2,prq,"Promoting |Junior| to Senior, showing good results",,x

To get it using miller you can change format from csvlite to csv and run

mlr --csv -N put '$3=gsub($3,"\"","|")' input.csv

Hi @Aborruso, As mentioned earlier, the fix need to be done during data load from ADLS to database while loading the data. Thank you anyway for the solution provided. — sachin, Jun 26 '23 at 07:49

score -1 · Answer 2 · edited Jul 05 '23 at 05:26

-1

Thank you for all your response and solutions provided.

The issue has been fixed by change in below settings at Copy activity of ADF pipeline.

edited Jul 05 '23 at 05:26

karel

5,489
46
45
50

answered Jun 26 '23 at 07:45

sachin

1

Remove double quote within double quoted string using Replace command

2 Answers2