Removing multiple delimiters between outside delimiters on each line

Question

Using awk or sed in a bash script, I need to remove comma separated delimiters that are located between an inner and outer delimiter. The problem is that wrong values ends up in the wrong columns, where only 3 columns are desired.

For example, I want to turn this:

2020/11/04,Test Account,569.00
2020/11/05,Test,Account,250.00
2020/11/05,More,Test,Accounts,225.00

Into this:

2020/11/04,Test Account,569.00
2020/11/05,Test Account,250.00
2020/11/05,More Test Accounts,225.00

I've tried to use a few things, testing regex: But I cannot find a solution to only select the commas in order to remove.

Nobody has suggested you fix the upstream process that is generating the incorrect CSV data. — glenn jackman, Dec 30 '20 at 17:25
There are bunch of answers using regex here so it is good idea to keep this tag to enhance search capability of this question — anubhava, Feb 06 '21 at 12:03
Thanks anubhav sir for adding the tag, OP has stated its a regex question not sure why tag is being removed. — RavinderSingh13, Feb 06 '21 at 12:05

score 9 · Accepted Answer · answered Nov 11 '20 at 13:07

awk -F, '{ printf "%s,",$1;for (i=2;i<=NF-2;i++) { printf "%s ",$i };printf "%s,%s\n",$(NF-1),$NF }' file

Using awk, print the first comma delimited field and then loop through the rest of the field up to the last but 2 field printing the field followed by a space. Then for the last 2 fields print the last but one field, a comma and then the last field.

Ed Morton · Answer 2 · 2020-11-11T13:29:37.957

With GNU awk for the 3rd arg to match():

$ awk -v OFS=, '{
     match($0,/([^,]*),(.*),([^,]*)/,a)
     gsub(/,/," ",a[2])
     print a[1], a[2], a[3]
}' file
2020/11/04,Test Account,569.00
2020/11/05,Test Account,250.00
2020/11/05,More Test Accounts,225.00

or with any awk:

$ awk '
    BEGIN { FS=OFS="," }
    {
        n = split($0,a)
        gsub(/^[^,]*,|,[^,]*$/,"")
        gsub(/,/," ")
        print a[1], $0, a[n]
    }
' file
2020/11/04,Test Account,569.00
2020/11/05,Test Account,250.00
2020/11/05,More Test Accounts,225.00

Timur Shtatland · Answer 3 · 2020-11-11T13:37:08.270

Use this Perl one-liner:

perl -F',' -lane 'print join ",", $F[0], "@F[1 .. ($#F-1)]", $F[-1];' in.csv

The Perl one-liner uses these command line flags:
-e : Tells Perl to look for code in-line, instead of in a file.
-n : Loop over the input one line at a time, assigning it to $_ by default.
-l : Strip the input line separator ("\n" on *NIX by default) before executing the code in-line, and append it when printing.
-a : Split $_ into array @F on whitespace or on the regex specified in -F option.
-F',' : Split into @F on comma, rather than on whitespace.

$F[0] : first element of the array @F (= first comma-delimited value).
$F[-1] : last element of @F.
@F[1 .. ($#F-1)] : elements of @F between the second from the start and the second from the end, inclusive.
"@F[1 .. ($#F-1)]" : the above elements, joined on blanks into a string.
join ",", ... : join the LIST "..." on a comma, and return the resulting string.

SEE ALSO:
perldoc perlrun: how to execute the Perl interpreter: command line switches

score 7 · Answer 4 · answered Nov 11 '20 at 14:27

perl -pe 's{,\K.*(?=,)}{$& =~ y/,/ /r}e' file

sed -e ':a' -e 's/\(,[^,]*\),\([^,]*,\)/\1 \2/; t a' file

awk '{$1=$1","; $NF=","$NF; gsub(/ *, */,","); print}' FS=, file

awk '{for (i=2; i<=NF; ++i) $i=(i>2 && i<NF ? " " : ",") $i} 1' FS=, OFS= file

RavinderSingh13 · Answer 5 · 2020-11-11T13:20:01.043

6

awk doesn't support look arounds, we could have it by using match function of awk; using that could you please try following, written and tested with shown samples in GNU awk.

awk '
match($0,/,.*,/){
  val=substr($0,RSTART+1,RLENGTH-2)
  gsub(/,/," ",val)
  print substr($0,1,RSTART) val substr($0,RSTART+RLENGTH-1)
}
' Input_file

edited Nov 11 '20 at 13:20

answered Nov 11 '20 at 12:58

RavinderSingh13

130,504
14
57
93

score 6 · Answer 6 · answered Nov 11 '20 at 15:10

Yet another perl

$ perl -pe 's/(?:^[^,]*,|,[^,]*$)(*SKIP)(*F)|,/ /g' ip.txt
2020/11/04,Test Account,569.00
2020/11/05,Test Account,250.00
2020/11/05,More Test Accounts,225.00

(?:^[^,]*,|,[^,]*$) matches first/last field along with the comma character
- (*SKIP)(*F) this would prevent modification of preceding regexp
|, provide , as alternate regexp to be matched for modification

With sed (assuming \n is supported by the implementation, otherwise, you'll have to find a character that cannot be present in the input)

sed -E 's/,/\n/; s/,([^,]*)$/\n\1/; y/,/ /; y/\n/,/'

s/,/\n/; s/,([^,]*)$/\n\1/ replace first and last comma with newline character
y/,/ / replace all comma with space
y/\n/,/ change newlines back to comma

score 1 · Answer 7 · answered Dec 30 '20 at 17:34

A similar answer to Timur's, in awk

awk '
    BEGIN { FS = OFS = "," }
    function join(start, stop, sep,     str, i) {
        str = $start
        for (i = start + 1; i <= stop; i++) {
            str = str sep $i
        }
        return str
    }
    { print $1, join(2, NF-1, " "), $NF }
' file.csv

It's a shame awk doesn't ship with a join function builtin

Removing multiple delimiters between outside delimiters on each line

7 Answers7