Replace values in last column

Question

I have multiple text in file.txt

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I 30
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 40
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness 30
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA 30
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 40
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness 30
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit 20
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit 40
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 40
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I 10
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW 40
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit 40
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness 30
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness 30
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S 40
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest 50
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness 40
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN 40
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D 10
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ 40

10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,

I tried to use sed 's/\b30\b/View/g' file.txt , but there was a change throughout the file and i only need to change the last column.

I need to change the text to look like this

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal acces
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal acces
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

user_10785975 · Answer

I would use GNU AWK for this task following way, let file.txt content be

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I 30
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 40
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness 30
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA 30
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 40
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness 30
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit 20
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit 40
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 40
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I 10
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW 40
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit 40
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness 30
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness 30
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S 40
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest 50
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness 40
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN 40
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D 10
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ 40

then

awk 'BEGIN{a[10]="Minimal acces";a[20]="Guest";a[30]="View";a[40]="Reporter";a[50]="Owner"}{$NF=a[$NF];print}' file.txt

gives output

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal acces
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal acces
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

Explanation: In BEGIN I create array a with replacements as described in requirements, then for each line I use value from a to replace value of last field ($NF) and print such changed line. Disclaimer: this solution assumes that value of last field is always one present in a.

(tested in gawk 4.2.1)

user_1745001 · Answer

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN {
    n = split("10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner",tmp,/ *, */)
    for (i in tmp) {
        old = new = tmp[i]
        sub(/ .*/,"",old)
        sub(/[^ ]* */,"",new)
        map[old] = new
    }
}
$NF in map {
    $NF = map[$NF]
}
{ print }

$ awk -f tst.awk file
4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal acces
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal acces
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

user_4177828 · Answer

mapping="10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,  "

awk -v map="$mapping" '
        BEGIN {
                split(map, a, ","); 
                for (i in a) { 
                        num  = gensub(/^([ ]*)?([^ ]*)([ ]*)?(.*)$/, "\2", "g", a[i]) 
                        desc = gensub(/^([ ]*)?([^ ]*)([ ]*)?(.*)$/, "\4", "g", a[i])
                        newnf[num] = desc
                }
        }
        {$NF = newnf[$NF]}1
' input_file

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal acces
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal acces
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

Other solution

mapping="10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,  "
awk -v map="$mapping" '
        NR==FNR{ n=$1; $1=""; gsub(/^ /,"",$0); a[n]=$0; next}
        { $NF=a[$NF] }1
' <(tr ',' '
' <<<"$mapping") input_file

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal acces
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal acces
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

user_967492 · Answer

This might work for you (GNU sed):

lookup=" 10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,"
sed -E 's/$/
'"${lookup}"'/;s/( \S+)
.*\1( [^,]+).*/\2/;P;d' file

Append a lookup table to each line and using regexp back references, replace the last field in each line with its match.

N.B. If no match is found the line is printed as is.

user_3832970 · Answer

You can use

sed 's/$[[:space:]]$30[[:space:]]*$/\1View/' file.txt > newfile.txt

Here, the 30 number is matched only at the end of the string ($) and when preceded with a whitespace.

Details:

$[[:space:]]$ - Capturing group 1 (\1 in the replacement pattern refers to this group value): a whitespace
30 - a fixed string
[[:space:]]* - zero or more (trailing) whitespaces
$ - end of string.

See an online demo:

#!/bin/bash
s='4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I 30
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 40
10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,'
sed 's/$[[:space:]]$30[[:space:]]*$/\1View/' <<< "$s"

Output:

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 40
10 Minimal acces, 20 Guest, 30 View, 40 Reporter, 50 Owner,

user_14672114 · Answer

As long as you're not concerned about existing extra padded-spaces being squeezed away :

{m,g}awk '
BEGIN {
    __[___ =_+= _+= (_+=_^=_<_) \
        +_--] = "Minimal access"
    __[_+=+_] = "Guest"
    __[_+=+_] = "Reporter"
    __[_+___] = "Owner"
    __[_-___] = "View"  } $NF = __[$NF]'

|

4141 2019-01-08T14:42:55.000+02:00 JonhSmith LS08EE0I View
2128 2019-11-02T13:47:34.000+02:00 James Davis RT84SO1 Reporter
2293 2019-12-21T17:41:37.000+02:00 James Davis bissness View
1931 2019-12-15T12:16:48.000+02:00 James Davis IL44DEAA View
2124 2019-10-12T15:23:46.000+03:00 James Davis AA4074S21 Reporter
2035 2019-12-09T15:33:28.000+02:00 James Davis bissness View
4843 2022-03-02T12:48:34.000+02:00 Wilson Robert JR autotesit Guest
5361 2022-03-02T12:44:55.000+02:00 Wilson Robert JR autotesit Reporter
2135 2019-10-12T21:06:30.000+03:00 James Davis FR4SA21 Reporter
2122 2019-12-23T20:10:06.000+02:00 Administrator QQ2366I Minimal access
2123 2019-10-12T15:40:16.000+03:00 James Davis LS1d0784EW Reporter
5075 2022-03-02T12:49:10.000+02:00 Lee Patricia JR autotesit Reporter
2224 2019-12-20T16:26:36.000+02:00 James Davis G1bissness View
2582 2021-06-20T15:07:19.000+03:00 Jame E2bissness View
2121 2019-10-12T17:12:38.000+03:00 James Davis AZ1878S Reporter
4694 2022-06-20T16:00:48.000+03:00 Oliver A autotest Owner
2076 2019-12-02T18:32:42.000+02:00 James Davis bissness Reporter
2694 2021-04-23T11:42:58.000+03:00 Scott Harper JR AZ0410MAN Reporter
1721 2019-07-13T15:30:56.000+03:00 Hall Braylon AZ14089D Minimal access
1863 2019-07-25T15:45:02.000+03:00 Diaz Thomas AZ141IJ Reporter

Replace values in last column

6 Answers