mjhjmtu

I have a input file delimited with commas (,). There are some fields enclosed in double quotes that are having a comma in them. Here is the sample row

123,"ABC, DEV 23",345,534.202,NAME

I need to remove all the comma's that are not occuring within the double quotes by a ~
So the output should be like:

123~"ABC, DEV 23"~345~534.202~NAME

I have tried this, but it gives me reverse output:

awk -F '"' -v OFS='' ' for (i=0; i<= NF; ++i) gsub(",","~",$i) 1' test.txt
123,ABC~ DEV 23,345,534.202,NAME

You basically have a CSV file that you would like to replace the delimiter in, from , to ~.

Using csvkit:

$ csvformat -D '~' file.csv >newfile.csv

$ cat newfile.csv
123~ABC, DEV 23~345~534.202~NAME

cvsformat removes the quotation marks that are not needed. To add quotation marks:

$ csvformat -U 1 -D '~' file.csv
"123"~"ABC, DEV 23"~"345"~"534.202"~"NAME"

See csvformat --help for usage info.

GNU awk solution:

awk -v FPAT='[^,]+|"[^"]+"' ' for(i=1;i<=NF;i++) printf "%s%s",$i,(i<NF? "~" : ORS) ' file

• 
  FPAT='[^,]+|"[^"]+"' - regex pattern describing each field is either “anything that is not a comma,” or “a double quote, anything that is not a double quote, and a closing double quote.”
  

The output:

123~"ABC, DEV 23"~345~534.202~NAME

I have done by below 2 methods for above example . Tested

Method1

for (( i=1;i<6;i++)); do awk -F "," -v i="$i" '$i ~ /"/gsub(" ",",",$2);print ' inputfile;done| tail -1| sed 's/,/~/3g'| sed 's/,/~/1'

output

123~"ABC, DEV 23"~345~534.202~NAME'

Method2

 sed "s/,/~/3g" inputfile| sed 's/,/~/1'

Output

123~"ABC, DEV 23"~345~534.202~NAME'

You can try this awk

awk 'NR%2==1gsub(",","~")1' RS='"' ORS='"' infile

In case you don't want to install a special package, you can probably the csv parser of a preinstalled ruby:

ruby -e 'require "csv"; CSV.filter(output_col_sep: "~") '

Examples:

1,"2,3"

results in

1~2,3

and

1,"
2,3
",4

results in

1~"
2,3
"~4

This is opposite of this question that I had answer there too, it's can be done by starting loop step value from 1.

awk -F" 'for (i=1;i<=NF;i+=2)gsub(",","~",$i)1' OFS=" infile

Output is:

123~"ABC, DEV 23"~345~534.202~NAME

awk 'sub(/,/,"~")gsub(/,/,"~",$3)1' file

output
123~"ABC, DEV 23"~345~534.202~NAME

The very first comma is replaced by sub and the rest by gsub in third field.