Matching the last K occurrences of a pattern in a line

Question

Is it possible using sed/awk to match the last k occurrences of a pattern in a line?

For simplicity's sake, say I just want to match the last 3 commas in each line, for example (note that the two lines have a different number of total commas):

10, 5, "Sally went to the store, and then , 299, ABD, F, 10
10, 6, If this is the case, and also this happened, then, 299, A, F, 9

I want to match only the commas starting from 299 until the end of the line in both bases.

Motivation: I'm trying to convert a CSV file with stray commas inside one of the fields to tab-delimited. Since the number of proper columns is fixed, my thinking was to replace the first couple commas with tabs up until the troublesome field (which is straightforward), and then go backwards from the end of the line to replace again. This should convert all proper delimiter commas to tabs, while leaving commas intact in the problematic field.

There's probably a smarter way to do this, but I figured this would be a good sed/awk teaching point anyways.

Answer 1

another sed alternative. Replace last 3 commas with tabs

$ rev file | sed 's/,/\t/;s/,/\t/;s/,/\t/' | rev

10, 5, "Sally went to the store, and then , 299  ABD     F       10

with GNU sed, you can simply write

$ sed 's/,/\t/g5' file

10, 5, "Sally went to the store, and then , 299  ABD     F       10

replace all starting from 5th.

Answer 2

One regex that matches each of the three last commas separately would require a negative lookahead, which sed does not support. You can use the following sed-regex to match the last three fields and the commas directly before them all at once:

,[^,]*,[^,]*,[^,]*$

$ matches the end of the line.

[^,] matches anything but ,.

Groups allow you to re-use the field values in sed:

sed -r 's/,([^,]*),([^,]*),([^,]*)$/\t\1\t\2\t\3/'

For awk, have a look at How to print last two columns using awk.

There's probably a smarter way to do this

In case all your wanted commas are followed by a space and the unwanted commas are not, how about

sed 's/,[^ ]/./g'

This transforms a, b, 12,3, c into a, b, 12.3, c.

Answer 3

You can use Perl to add the missing double quote into each line:

perl -aF, -ne '$F[-5] .= q("); print join ",", @F' < input > output

or, to turn the commas into tabs:

 perl -aF'/,\s/' -ne 'splice @F, 2, -4, join ", ", @F[ 2 .. $#F - 4 ]; print join "\t", @F' < input > output

• -n reads the input line by line.
• -a splits the input into the @F array on the pattern specified by -F.
• The first solution adds the missing quote to the fifth field from the right; the second one replaces the items from the third to the fifth from right with those elements joined by ", ", and separates the resulting array with tabs.

Answer 4

To fix the CSV, I would do this:

echo '10, 5, "Sally went to the store, and then , 299, ABD, F, 10' |
  perl -lne '
    @F = split /, /;             # field separator is comma and space
    @start = splice @F, 0, 2;    # first 2 fields
    @end = splice @F, -4, 4;     # last 4 fields
    $string = join ", ", @F;     # the stuff in the middle
    $string =~ s/"/""/g;         # any double quotes get doubled
    print join(",", @start, "\"$string\"", @end);
  '

outputs

10,5,"""Sally went to the store, and then ",299,ABD,F,10

Answer 5

Hi I guess this is doing the job

echo 'a,b,c,d,e,f' | awk -F',' '{i=3; for (--i;i>=0;i--) {printf "%s\t", $(NF-i) } print ""}'

Returns

d    e    f

But you need to ensure you have more than 3 arguments

Answer 6

This will do what you're asking for with GNU awk for the 3rd arg to match():

$ cat tst.awk
{
    gsub(/\t/," ")
    match($0,/^(([^,]+,){2})(.*)((,[^,]+){3})$/,a)
    gsub(/,/,"\t",a[1])
    gsub(/,/,"\t",a[4])
    print a[1] a[3] a[4]
}

$ awk -f tst.awk file
10       5       "Sally went to the store, and then , 299        ABD     F       10
10       6       If this is the case, and also this happened, then, 299  A       F       9

but I'm not convinced what you're asking for is a good approach so YMMV.

Anyway, note the first gsub() making sure you have no tabs on the input line - that is crucial if you want to convert some commas to tabs to use tabs as output field separators!