Replace the nth-from-end occurrence of string in each line

Question

Using sed, how can I replace the Nth to last occurrence of a character on each line of a given file?

In this case I want to replace the 3rd to last ; with ,

input

1;2;3;4;5;6;7;8;9
10;20;30;40;50;60;70;80;90
100;200;300;400;500;600;700;800;900

expected output

1;2;3;4;5;6,7;8;9
10;20;30;40;50;60,70;80;90
100;200;300;400;500;600,700;800;900

I know I could replace the 6th occurrence like this

sed 's/;/,/6' input_file.csv > output_file.csv

Or the last one

sed -r 's/(.*);/\1,/' input_file.csv > output_file.csv

But in my particular case and because of some nuances, I need to start from the end.

I've tried something like

sed -r 's/(.*);/\1,/3' input_file.csv > output_file.csv

Answer 1

You could simply reverse the lines, change the Nth occurrence, then re-reverse:

 rev file | sed 's/;/,/3' | rev

Answer 2

You could explicitly capture two more delimited fields:

$ sed -r 's/(.*);([^;]*;[^;]*;)/\1,\2/' input_file.csv
1;2;3;4;5;6,7;8;9
10;20;30;40;50;60,70;80;90
100;200;300;400;500;600,700;800;900

or more programatically

$ sed -r 's/(.*);(([^;]*;){2})/\1,\2/' input_file.csv
1;2;3;4;5;6,7;8;9
10;20;30;40;50;60,70;80;90
100;200;300;400;500;600,700;800;900

where the number in the quantifier {n-1} replaces the nth from last.

Answer 3

Using gawk:

awk -F';' '{print gensub(";", ",", NF-3)}'

The gawk built-in function gensub() replaces nth to last semicolon with comma.

Answer 4

sed is good when you're working with entire lines, but awk and perl are better when you need to work with fields/columns within a line.

$ perl -F';' -lane 'splice @F,-4,2,join(",",@F[-4..-3]); print join(";",@F)' input.csv 
1;2;3;4;5;6,7;8;9
10;20;30;40;50;60,70;80;90
100;200;300;400;500;600,700;800;900

This splits the line into the @F array, using ; as the separator. Then it replaces the fourth-last and third-last fields with a single string containing the values of both fields separated by a comma.

Then it prints the entire @F array with semi-colons as the field separator.

---

splice is perl's built-in general purpose array manipulation function. There are several forms for using it, but the form I'm using here is:

splice ARRAY,OFFSET,LENGTH,LIST

i.e.

splice @F, -4, 2, join(",",@F[-4..-3])

• ARRAY: @F, perl's default array when used with -F or -a options, similar to awk's $1, $2, $3 field numbers.
• OFFSET: -4, the fourth last field
• LENGTH: 2, two fields (i.e. $F[-4] and $F[-3])
• LIST: in this case the "LIST" has only a single element, a string containing the values of fields -4 and -3 joined by a comma. In general, though, LIST can be an array of any length, so splice can also be used to insert more fields into an array.

See perldoc -f splice for details.

Also, @F[-4..-3] is what's known in perl as an array slice. The range operator .. returns the elements of fields -4 through to -3. For this script, it could also have been written as @F[-4,-3], which is the elements -4 and -3. Array slices can be specified with ranges and/or lists.

Answer 5

With GNU sed we can do as follows.

• skip any further processing,print line, go back read next line if less than 3 semicolons in a line.
• change all semicolons to newlines.
• progressively revert the newlines one by one until we are left with 3 newlines.
• now change the first new line to comma and the rest reverted.

sed -e '
  s/;/&/3;T
  y/;/\n/

  :a;s/\n/&/4;tb
   s//,/;y/\n/;/
  :b;s//;/;ta
' file

1;2;3;4;5;6,7;8;9
10;20;30;40;50;60,70;80;90
100;200;300;400;500;600,700;800;900

---

With Perl we split and also record the field separators in the split up array (@F). Then the 6th field from the end is the 3rd semicolon from the end and which we change to comma. Rest is unchanged.

perl -aF'(;)' -plse '
  $F[-6]=",",s/.*/@F/ if @F >= 6;
' -- -\"= ./file

---

Using POSIX awk, we modify the separators based on their position:

awk -F\; -v n=3 '{ for(i=1; i<=NF; i++) printf "%s%s", $i, (i==NF-n?",":(i==NF?ORS:FS)) }' infile

Answer 6

A variation on steeldriver's answer that anchors the pattern at the end of the line and therefore can use 3 (or whatever number, n, you need to use) rather than 2 (i.e. n-1) for the repeat count of trailing fields, as long as there are actually that many fields. It also does not care about the initial data on the line, before the delimiter that we want to replace.

Using standard sed:

sed 's/;\(\([^;]*;\{0,1\}\)\{3\}\)$/,\1/' file

Using on extended regular expression in sed implementations that support it:

sed -E 's/;(([^;]*;?){3})$/,\1/' file

Either expression match the last three fields. The sub-expression ([^;]*;?) will match a string that contains no ; character, and then optionally ends with one such character. The last field on the line does not end with a ;, so this allows us to match that field and still anchor the full expression to the end of the line.

This, like most sed solutions, would not work if any field contains embedded ; characters or newlines (which are allowed in CSV files).

---

Using a CSV parser to convert the data into JSON, modifying it with jq, and then turning it back into a CSV file with the correct delimiters. This would cope with data containing embedded delimiters, newlines, and quotes:

csvjson -H -d ';' file |
jq -r --argjson n 3 '.[] | [.[]] | .[-($n+1):(if $n > 1 then -($n-1) else null end)]] |= [join(",")] | @csv' |
csvformat -H -D ';'

This uses csvkit to turn the CSV into JSON. It then imports the value 3 into the jq variable n on the command line and uses it to join the two fields based on this value. The modified data is then turned back into CSV and reformatted to use ; as delimiters as from the start.

Given a CSV file with four columns and four rows, like

A;B;C;D
1;"2.1;2.2";3;4
1;2;"3.1
3.2
3.3";4
1;"And then I said ""2 2 2""";3;4

the second delimiter from the end is changed to a comma (effectively merging the 2nd and 3rd fields into a single field):

$ csvjson -H -d ';' file | jq -r --argjson n 2 '.[] | [.[]] | .[-($n+1):(if $n > 1 then -($n-1) else null end)] |= [join(",")] | @csv' | csvformat -D ';'
A;B,C;D
1;"2.1;2.2,3";4
1;"2,3.1
3.2
3.3";4
1;"And then I said ""2 2 2"",3";4