Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 occuring within inside the double quotes and the double quotes as well. So the above line should get parsed into as shown below

123,ABC DEV 23,345,534.202,NAME

I tried the following using sed but not giving expected results.

sed -e 's/\(".*\),\(".*\)/\1 \2/g'

Any quick tricks with sed, awk or any other unix utility please?

share|improve this question
I'm not sure what you're trying to do, but the utility "csvtool" is far better for parsing csv than generic tools like sed or awk. It's in just about every distro of linux. – Tim Kelley Nov 11 '15 at 21:42
up vote 20 down vote accepted

If the quotes are balanced, you will want to remove commas between every other quote, this can be expressed in awk like this:

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


123,ABC DEV 23,345,534.202,NAME


The -F" makes awk separate the line at the double-quote signs, which means every other field will be the inter-quote text. The for-loop runs gsub, short for globally substitute, on every other field, replacing comma (",") with nothing (""). The 1 at the end invokes the default code-block: { print $0 }.

share|improve this answer
Please can you elaborate on gsub and explain in short, how this one liner works?? please. – mtk Sep 20 '12 at 11:46
Thank you! This scripts works really well, but could you explain the lonely 1 at the end of the script? -- } 1' -- – CocoaEv Jun 18 '14 at 23:59
@CocoaEv: It executes { print $0 }. I added that to the explanation as well. – Thor Jun 22 '14 at 13:26

There is a good response, using sed simply one time with a loop:

echo '123,"ABC, DEV 23",345,534,"some more, comma-separated, words",202,NAME'|
  sed ':a;s/^\(\([^"]*,\?\|"[^",]*",\?\)*"[^",]*\),/\1 /;ta'
123,"ABC  DEV 23",345,534,"some more  comma-separated  words",202,NAME


  • :a; is a label for furter branch
  • s/^\(\([^"]*,\?\|"[^",]*",\?\)*"[^",]*\),/\1 / could contain 3 enclosed parts
    • first the 2nd: [^"]*,\?\|"[^",]*",\? match for a string containing no double quote, maybe followed by a coma or a string enclosed by two double quote, without coma and maybe followed by a coma.
    • than the first RE part is composed by as many repetition of previously described part 2, followed by 1 double quote and some caracteres, but no double-quote, nor comas.
    • The first RE part as to be followed by a coma.
    • Nota, the rest of the line don't need to be touched
  • ta will loop to :a if previous s/ command did some change.
share|improve this answer
Works also with nested quotes. Awesome, thanks! – tricasse May 27 '15 at 17:47

A general solution that can also handle several commas between balanced quotes needs a nested substitution. I implement a solution in perl, which process every line of a given input and only substitute commas in every other pair of quotes:

perl -pe 's/ "  (.+?  [^\\])  "               # find all non escaped 
                                              # quoting pairs
                                              # in a non-greedy way

           / ($ret = $1) =~ (s#,##g);         # remove all commas within quotes
             $ret                             # substitute the substitution :)

or in short

perl -pe 's/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge'

You can either pipe the text you want to process to the command or specify the textfile to be processed as last command line argument.

share|improve this answer
The [^\\] is going to have the undesired effect of matching the last character inside the quotes and removing it (non \ character), i.e., you should not consume that character. Try (?<!\\) instead. – tojrobinson Sep 20 '12 at 9:13
Thanks for your objection, I have corrected that. Nevertheless I think we don't need look behind assertion here, or do we!? – user1146332 Sep 20 '12 at 9:26
Including the non \ in your capture group produces an equivalent result. +1 – tojrobinson Sep 20 '12 at 9:31
+1. after trying a few things with sed, I checked sed's docs and confirmed that it can't apply a replace to just the matching portion of a line...so gave up and tried perl. Ended up with a very similar approach but this version uses [^"]* to make the match non-greedy (i.e. matches everything from one " to the next "): perl -pe 's/"([^"]+)"/($match = $1) =~ (s:,::g);$match;/ge;'. It does not acknowledge the outlandish idea that a quote might be escaped with a backslash :-) – cas Sep 20 '12 at 10:21
Thanks for your comment. Would be interesting if either the [^"]* approach or the explicit non-greedy approach consumes less cpu time. – user1146332 Sep 20 '12 at 10:53

I would use a language with a proper CSV parser. For example:

ruby -r csv -ne '
  CSV.parse($_) do |row|
    newrow = CSV::Row.new [], []
    row.each {|field| newrow << field.delete(",")}
    puts newrow.to_csv
' < input_file
share|improve this answer

Your second quotes are misplaced:

sed -e 's/\(".*\),\(.*"\)/\1 \2/g'

In addition, using regular expressions tend to match the longest possible part of the text, meaning this will not work if you have more than one quoted field in the string.

A way that handles multiple quoted fields in sed

sed -e 's/\(\"[^",]\+\),\([^",]*\)/\1 \2/g' -e 's/\"//g'

This is also a way to solve this, however, with input that may contain more than one comma per quoted field the first expression in the sed would have to be repeated as many times as the maximum comma content in a single field, or until it does not change the output at all.

Running sed with the more than one expression should be more efficient than several sed processes running and a "tr" all running with open pipes.

However, this may have undesired consequences if the input is not properly formatted. i.e. nested quotes, unterminated quotes.

Using the running example:

echo '123,"ABC, DEV 23",345,534,"some more, comma-separated, words",202,NAME' \
| sed -e 's/\(\"[^",]\+\),\([^",]*\)/\1 \2/g' \
-e 's/\(\"[^",]\+\),\([^",]*\)/\1 \2/g' -e 's/\"//g'


123,ABC  DEV 23,345,534,some more  comma-separated  words,202,NAME
share|improve this answer
You can make it more general with conditional branching and more readable with ERE, e.g. with GNU sed: sed -r ':r; s/("[^",]+),([^",]*)/\1 \2/g; tr; s/"//g'. – Thor Feb 25 '13 at 12:56

In perl - you can use Text::CSV to parse this, and do it trivially:

#!/usr/bin/env perl
use strict;
use warnings;

use Text::CSV; 

my $csv = Text::CSV -> new();

while ( my $row = $csv -> getline ( \*STDIN ) ) {
    #remove commas in each field in the row
    $_ =~ s/,//g for @$row;
    #print it - use print and join, rather htan csv output because quotes. 
    print join ( ",", @$row ),"\n";

You can print with Text::CSV but it tends to preserve quotes if you do. (Although, I'd suggest - rather than stripping quotes for your output, you could just parse using Text::CSV in the first place).

share|improve this answer

I created a function to loop thru every character in the string.
If the character is a quotation then the check (b_in_qt) is marked true.
While b_in_qt is true, all commas are replaced with a space.
b_in_qt is set to false when the next comma is found.

str_out     varchar2(1000)  := null;
str_chr     varchar2(1)     := null;
b_in_qt     boolean         := false;

    FOR x IN 1..length(str_in) LOOP
      str_chr := substr(str_in,x,1);
      IF str_chr = '"' THEN
        if b_in_qt then
            b_in_qt := false;
            b_in_qt := true;
        end if;
      END IF;
      IF b_in_qt THEN
        if str_chr = ',' then
            str_chr := ' ';
        end if;
      END IF;
    str_out := str_out || str_chr;
RETURN str_out;

str_in := f_replace_c ("blue","cat,dog,horse","",yellow,"green")

  "blue","cat dog horse","",yellow,"green"
share|improve this answer

protected by Community Jun 6 at 18:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.