How to replace single or double space in a text file when between quotes

Question

I have a CSV file with some fields quoted, but the entries within quotes are separated by double or single spaces. I need these to be replaced with commas.

Example line:

This is okay,"ABC DEF GHI",123,"This is not okay",remove,spaces,within,quotes

And how it should look:

This is okay,"ABC,DEF,GHI",123,"This,is,not,okay",remove,spaces,within,quotes

Answer 1

perl -pe 's/".*?"/do{$a = $&; $a =~ s: +:,:g; $a}/ge;'

Essentially, this is just a global regex replacement s/regex/replacement/g. The regex is ".*?", it matches every substring that starts with " and ends with the next following ". The tricky parts are:

• The replacement is not a string, but an expression that is evaluated. (That's the meaning of the e modifier after g.)
• The expression that is evaluated is again a global regex replacement s:regex:replacement:g that replaces any non-empty sequence of spaces by a comma. (We cannot use the same delimiter as in the outer replacement, so we use : instead of /.)
• In order to execute the inner regex replacement, we have to assign the matched substring of the outer replacement $& to some other variable $a, then perform the inner replacement on $a, and finally print $a.

With a sufficiently recent perl version, the assignment to an auxiliary variable can be avoided. Using the r modifier, the inner replacement can be performed directly on a copy of the matched substring $& (thanks to Stéphane Chazelas):

perl -pe 's/".*?"/$&=~s: +:,:gr/ge;'

Answer 2

Consider this brute-force awk solution:

awk -F, -v OFS=, '
  {
    for(i=1;i<=NF;i++)
        if ($i ~ /^".*"$/)
                gsub(" +", ",", $i)
    print $0
  }'

It tells awk to split the records on commas -- noting that this will break if any of your fields contains a comma! -- and using OFS to tell the print statement to recombine the fields with commas. The for loop goes through each field of the line, and if the field starts ^ with a double-quote, has any characters .* and ends $ with a double-quote, then globally replace in that $i field any number of spaces with commas. After looping over the fields, print the whole record ($0).

Answer 3

With GNU awk:

gawk -v RS=\" '
  NR % 2 == 0{gsub(/ +/, ",")}
  {ORS = RT; print}'

That is, the record separator is made the " character and whitespace are only replaced on even records.

RT is the GNU-specific part.

Same with GNU sed:

tr '\n"' '"\n' | sed -E '2~2s/ +/,/g' | tr '"\n' '\n"'

More portably:

tr '\n"' '"\n' | sed 'n;s/  */,/g' | tr '"\n' '\n"'

would work with some other seds though you could have issues if the last character of the input is not a ".