0

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”
4
  • 1
    Can you confirm your data actually uses over "?
    – FelixJN
    Commented Oct 4, 2022 at 9:12
  • Sorry for typo, it’s normal double quotes only.
    – John
    Commented Oct 4, 2022 at 12:07
  • 2
    John please fix your data so it matches reality. In particular, the "smart" quotes (double and single). It's unreasonable to expect us to guess what your data actually looks like Commented Oct 4, 2022 at 12:35
  • I’m voting to close this question because the poster still hasn't fixed their data and has seemingly abandoned the question Commented Oct 8, 2022 at 23:43

2 Answers 2

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.

3
  • Hi Kusalananda, i have not tried csvkit tool because i have some limitations and do not have package installed for csvformat command and i am getting error as ‘command not found’. So i was using awk utility. Please help me using sed/awk.
    – John
    Commented Oct 4, 2022 at 12:18
  • @John In that case, you will have to implement at least a partial CSV parser in awk or sed. I will not do this here. Your other option is to wait for another answer that better fits your needs.
    – Kusalananda
    Commented Oct 4, 2022 at 12:21
  • @John I added another solution using another CSV-aware tool called mlr.
    – Kusalananda
    Commented Oct 4, 2022 at 12:46
0

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.

2
  • 3
    It also assumes there are no embedded double quotes next to an internal comma, and that the embedded commas are not first or last within the quoted field.
    – Kusalananda
    Commented Oct 4, 2022 at 13:04
  • 1
    sed -i ... does not edit the file in-place. It creates a temporary file, then renames the temporary file to the original name, deleting the original file. Commented Oct 4, 2022 at 13:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .