Add a quote before/after n-th occurrence of a comma

Question

I have a file in this format:

a1,b1,c1,d1,e1,f1
a2,b2,c2,d2,e2,f2

I want to add quotes (") around specific columns (say column a and column d, columns do not contain other commas, but they are of different lengths) to get something like:

"a1",b1,c1,"d1",e1,f1
"a2",b2,c2,"d2",e2,f2

I was trying something like below to execute in multiple iterations to replace commas (,) with commas+quotes (,") or with quotes+comma (",) depending if it was the beginning or end of a column:

sed -E 's/(([^,]*,){1}[^,]*),/\1\,\"/g'

But this will replace every 2nd comma with a comma and quotes, while I want with each command to control only one place where I add the quotes.

Answer 1

First off, you don't want g, that means "global", replace all occurrences, and since you need to specify a column, you don't want to replace all. Next, you can target the Nth occurrence in sed like this: s/old/new/N where N is the Nth occurrence. So, to quote the 4th field, you would do:

$ sed 's/[^,]*/"&"/4' file 
a1,b1,c1,"d1",e1,f1
a2,b2,c2,"d2",e2,f2

And to change the first field:

$ sed 's/[^,]*/"&"/1' file 
"a1",b1,c1,d1,e1,f1
"a2",b2,c2,d2,e2,f2

The & is a special sed variable that means "whatever was matched by the left hand side of the s/// operator".

Answer 2

awk is good with fields (myself not so much -- this works on fields 1 and 3, not 1 and 4).

Awk='{
    sub(/.*/, Dq "&" Dq, $1);
    sub(/.*/, Dq "&" Dq, $3);
    print;
}'

$ awk -v FS=, -v OFS=, -v Dq='"' "${Awk}" <<'[][]'
a1,b1,c1,d1,e1,f1
a2,b2,c2,d2,e2,f2
[][]

"a1",b1,"c1",d1,e1,f1
"a2",b2,"c2",d2,e2,f2

Answer 3

Pass a list of the fields to quote

awk -v fields='1,4' '
    BEGIN {
        FS = OFS = ","
        n = split(fields, fs)
    }
    { for (i=1; i<=n; i++) $(fs[i]) = "\"" $(fs[i]) "\"" }
    { print }
' file

"a1",b1,c1,"d1",e1,f1
"a2",b2,c2,"d2",e2,f2

Answer 4

Nowadays i use Perl instead of sed because every modern distro has it available and it's more powerful:

perl -pne 's/^(\w*?),(\w*?),(\w*?),(\w*?),(\w*?),(\w*?)$/"$1",$2,$3,"$4",$5,$6/' < inputfile > outputfile

or if you don't want a extra file:

perl -pi -e 's/^(\w*?),(\w*?),(\w*?),(\w*?),(\w*?),(\w*?)$/"$1",$2,$3,"$4",$5,$6/' file

Or shorter but less readable:

perl -pi -e 's/^(\w*?),(\w*?,\w*?),(\w*?),(\w*?,\w*?)$/"$1",$2,"$3",$4/' file

Or depending on your real input this might be more correct:

perl -pi -e 's/^(.*),(.*),(.*),(.*),(.*),(.*)$/"$1",$2,$3,"$4",$5,$6/' file

It all depends on what the file really looks like.

Answer 5

Using awk or Miller (mlr), with the field numbers that you want to quote given as a comma-delimited list on the command line:

$ awk -F, -v f=1,4,5 'BEGIN { OFS=FS; split(f,a,",") } { for (i in a) $a[i] = "\"" $a[i] "\"" };1' file
"a1",b1,c1,"d1","e1",f1
"a2",b2,c2,"d2","e2",f2

$ mlr --nidx --fs comma put -s f=1,4,5 'begin { @a=splitnv(@f,",") } for (k,v in @a) { $[v] = "\"" . $[v] . "\"" }' file
"a1",b1,c1,"d1","e1",f1
"a2",b2,c2,"d2","e2",f2

Both are equivalent in perceiving the input as a "simple CSV" set of comma-delimited records (no field has embedded delimiters or newlines).

They split up the given string of numbers, where each number corresponds to a field that should be quoted. Then they iterate over these fields and modify each by adding quotes.

Answer 6

put in a script:

while IFS=',' read C1 C2 C3 C4 C5 C6
do
echo \"$C1\",$C2,$C3,\"$C4\",$C5,$C6 
done

and execute with "script < data"

If you prefer one-liners:

while IFS=',' read c1 c2 c3 c4 c5 c6; do echo $c1,$c2,\"$c3\",$c4,$c5,$c6; done < data

The numbers of arguments to 'read' must be greater than the number of the last column to quote.

Answer 7

Using Raku (formerly known as Perl_6)

~$ raku -pe 's:r:nth(1,4,6)/ [ <( <-[,]>* )> \,? ]+?  /"$<>"/;'  file

#OR

~$ raku -pe 's:r:nth(1,4,6)/ [ <( $<col> = <-[,]>* )> \,? ]+?  /"$<col>"/;'   file

Sample Input:

a1,b1,c1,d1,e1,f1
a2,b2,c2,d2,e2,f2
a3,b3,,d3,,
a4,b4,,,,

Sample Output:

"a1",b1,c1,"d1",e1,"f1"
"a2",b2,c2,"d2",e2,"f2"
"a3",b3,,"d3",,""
"a4",b4,,"",,""

Above answers are coded in Raku, a member of the Perl-family of programming languages. The example substitution is performed on the 1st, 4th, and 6th (last) columns of a Sample Input, to make sure each is properly quoted. Also, an advantage of the regex above is it will correctly handle cases where column values are empty (lines 3 and 4 of the Sample Input above).

Reading the first regex above, for the :nth(1,4,6) first and fourth and sixth instances of the pattern <-[,]>* zero-or-more non-comma containing characters (an empty/non-empty column) are slated to become the match object, since that pattern is encased in <(...)> Capture Markers, which drop all recognition elements outside.

From here, the regex demands that the column element is actually followed by \,? zero-or-one comma, and this column/comma group (denoted by square brackets) is frugally (+?) repeated one-or-more times. By setting the regex such that the comma is \,? zero-or-one, the last column is properly handled.

The regex above (for efficiency reasons) can be set to "non-backtracking" using the :r or :rachet adverb (a ratchet only moves in one direction). To clarify what the regex is doing, named regexes can be used (example 2), e.g. assigning each column to the $<col> named-regex. [Finally, similar to other answers, the code only handles "Simple-CSV" and is not designed to handle embedded newlines or commas].

https://docs.raku.org/language/regexes
https://raku.org