I have an input file in CSV format. There are some fields enclosed in double quotes that have a comma in them. Here is the sample row

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

I need to remove all the commas that are not occurring within the double quotes by a ~, so the output should be like this:

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

I have tried this, but it gives me the 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 will very unlikely have an answer. Best pratice on stack exchange is to show some work and some of your try. You should consult unix.stackexchange.com/tour You can edit your question to show what you've try so far.
    – Kiwy
    Feb 7, 2018 at 12:45

7 Answers 7


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.

Using Miller (mlr) instead:

$ mlr --csv -N --ofs '~' cat file.csv
123~ABC, DEV 23~345~534.202~NAME

Retaining the original quotes even though they are no longer needed:

$ mlr --csv -N --ofs '~' --quote-original cat file.csv
123~"ABC, DEV 23"~345~534.202~NAME

In both of these commands, the -N option is use to signal the fact that the input has no header and that the output shouldn't have one either. The --ofs option sets the output field separator.


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

You can try this awk

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

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'csv(in => csv(in => $*IN), out => $*OUT, sep => "~");' < Adhya.csv

Sample Input:

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

Sample Output:

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

This answer uses the Raku Programming Language. The Perl(5) module Text::CSV_XS is well-regarded, and a longtime author/maintainer of that module has gone on to develop Raku's Text::CSV module (H. Merijn Brand, personal communication).

Briefly, the high-level csv(...) command is used to take csv input via $*IN (stdin). Alternative separators can be specified here with sep => "\t" or similar.

Then, the output is send via $*OUT (stdout), changing the separator to ~ tilde with the parameter sep => "~".



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


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'


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


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


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

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: "~") {}'



results in




results in

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

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

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

  • The code may provide answer to the question, but any explanations will be highly appretiated Feb 23, 2018 at 7:40

You must log in to answer this question.

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