9

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
12

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.

0
13

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

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

Using gawk:

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

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

0
6

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.

2

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
0
0

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
17
  • Note that this answer still replaces the ; if less than n ; exist. Try echo "abc;def" | sed 's/;\(\([^;]*;\{0,1\}\)\{3\}\)$/,\1/' which outputs abc,def.
    – ImHere
    Jun 24 at 20:06
  • @Isaac Yes it does, which is why I was very careful to add "as long as there are actually that many fields". The second part of my answer does not behave in that way though, you'd be happy to hear, which is why I did not include that wording there.
    – Kusalananda
    Jun 24 at 20:07
  • @guest_7 Thanks for your contribution, but I would not answer with a GNU sed solution generally as I don't personally use GNU sed. The code also lacked explanation. You are free and most welcome to add that solution in your own answer though (which is already a GNU sed solution).
    – Kusalananda
    Jun 25 at 9:49
  • actually I was thinking of this sed 's/;\(\([^;]*\(;\|$\)\)\{3\}\)$/,\1/'
    – Jasen
    Jun 25 at 13:07
  • @Jasen You seem to be using GNU sed, right? That \| is an alternation, I believe, which sed ordinarily does not support in basic regular expressions. Isn't (;|$)$ the same as ;?$? I don't really use GNU sed, but if you find that this command works better, then by all means use that instead.
    – Kusalananda
    Jun 25 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.