Comma delimited file contain comma in value

Question

I have a file with data as below:

“A”,”Dept1”,”i am mahesh,working in it”,”1”
“B”,”Dept2”,”i am suresh, working in non it”,”2”

Output i want is:

A,Dept1,i am mahesh~working in it,1
B,Dept2,i am suresh~working in non it,2

I have tried below but it is replacing all , with ~ as below:

awk -F ‘“,”’ -v OFS=‘’ ‘{for (i=0;i<=NF;++i) gsub(“,”,”~”,$i)}1’ file

“A”~”Dept1”~”i am mahesh~working in it”~”1”
“B”~”Dept2”~”i am suresh~working in non it”~”2”

Answer 1

When working with structured document formats, use tools aware of that particular format, not generic text-processing tools.

In a CSV file, unless it's known to be "simple", quoted fields may contain commas and newlines. Quotes within quoted fields are doubled up ("").

To change the embedded commas into tilde (~), you ideally use a CSV-aware tool such as mlr (Miller) or csvkit.

Below, I'm assuming that the quoting character is the ordinary double quote ("), not the curly quotes you're using in the question text (”).

This is transforming the 3rd field with mlr, which is a handy tool that is aware of several different structured formats:

$ mlr --csv -N put '$3=sub($3,",","~")' file
A,Dept1,i am mahesh~working in it,1
B,Dept2,i am suresh~ working in non it,2

The --csv option causes mlr to input and output CSV data, and -N tells the utility that our data has no header. We then apply the put "verb" (action) and give it an expression that ought to be looking familiar if you're used to awk (the order of the arguments to sub() is different, though).

---

With csvformat (from csvkit), we can reformat the data so we may more easily get to the commas we want to change.

I'm first changing the delimiters to @ (any character that is not already part of the data):

$ csvformat -D '@' file
A@Dept1@i am mahesh,working in it@1
B@Dept2@i am suresh, working in non it@2

I may then simply use tr to change the remaining commas to tilde:

$ csvformat -D '@' file | tr ',' '~'
A@Dept1@i am mahesh~working in it@1
B@Dept2@i am suresh~ working in non it@2

Then switch back the delimiters to commas using csvformat again:

$ csvformat -D '@' file | tr ',' '~' | csvformat -d '@'
A,Dept1,i am mahesh~working in it,1
B,Dept2,i am suresh~ working in non it,2

Note that this changes all embedded commas to tilde, not just the commas in the third column.

Redirect the result to a new name to save it to a file.

Answer 2

Try sed and a) change all occurences of a comma NOT between double quotes to tilde, then b) remove all double quotes:

sed 's/\([^"]\),\([^"]\)/\1~\2/g;s/"//g' infile

Use -i to replace the file inplace if the results are fine.

Assumption is that all fields are enclosed in quotes before usage.