How to translate a column value in the file using awk with tr command in unix

Question

Details:

Input file : file.txt

P123456789,COLUMN2
P123456790,COLUMN2
P123456791,COLUMN2

Expected output:

Z678999999,COLUMN2
Z678999995,COLUMN2
Z678999996,COLUMN2

If i try using a variable it is giving proper result.

(i.e) /tmp>echo "P123456789"|tr "0-9" "5-9"|tr "A-Z" "X-Z"
Z678999999

But if i do with awk command it is not giving result instead giving error:

/tmp>$ awk 'BEGIN { FS=OFS="," } { $1=echo $1|tr "0-9" "5-9"|tr "A-Z" "X-Z";$2="COLUMN2"); print }' /tmp/file.txt >/tmp/file.txt.tmp
awk: BEGIN { FS=OFS="," } { $1=echo $1|tr "0-9" "5-9"|tr "A-Z" "X-Z";$2="COLUMN2"); print }
awk:                                   ^ syntax error
awk: BEGIN { FS=OFS="," } { $1=echo $1|tr "0-9" "5-9"|tr "A-Z" "X-Z";$2="COLUMN2"); print }
awk:                                                  ^ syntax error
awk: BEGIN { FS=OFS="," } { $1=echo $1|tr "0-9" "5-9"|tr "A-Z" "X-Z";$2="COLUMN2"); print }
awk:                                                                             ^ syntax error

Can anyone help please?

awk is not shell, just like C is not shell. You can no more call a shell command from an awk program than you could call it from a C program. — Ed Morton, Feb 13 '14 at 22:14
Check this link: [calling-an-executable-program-using-awk](http://stackoverflow.com/questions/14634349/calling-an-executable-program-using-awk). You could also look up `getline` answers( see Kent's answer ), or learn enough awk to manipulate the data within awk entirely. — n0741337, Feb 13 '14 at 22:21

score 2 · Answer 1 · answered Feb 13 '14 at 22:18

2

just do what you wanted, without changing your logic:

awk line:

awk -F, -v OFS="," '{ "echo \""$1"\"|tr \"0-9\" \"5-9\"|tr \"A-Z\" \"X-Z\"" |getline $1}7'

with your data:

kent$  echo "P123456789,COLUMN2
P123456790,COLUMN2
P123456791,COLUMN2"|awk -F, -v OFS="," '{ "echo \""$1"\"|tr \"0-9\" \"5-9\"|tr \"A-Z\" \"X-Z\"" |getline $1}7'                                                              
Z678999999,COLUMN2
Z678999995,COLUMN2
Z678999996,COLUMN2

answered Feb 13 '14 at 22:18

Kent

189,393
32
233
301

It is still giving error : /tmp>$ cat aml_test.txt|awk -F, -v OFS="," '{ "echo \""$1"\"|tr \"0-9\" \"5-9\"|tr \"A-Z\" \"X-Z\"" |getline $1}7' sh: -c: line 0: unexpected EOF while looking for matching `"' sh: -c: line 1: syntax error: unexpected end of file P123456789,COLUMN2 P123456790,COLUMN2 P123456791,COLUMN2 – Murali Feb 13 '14 at 22:27
try the same command in my answer, see if it worked. don't know how your data look like. @Murali – Kent Feb 13 '14 at 22:29
+1, but what's the mysterious `7` at the end? Just a non-zero integer to create an always-true pattern that results in printing the current line? Did you mean to use `1`? – mklement0 Feb 14 '14 at 03:42
@mklement0 yes. but pressing 7 is easier for me than 1 – Kent Feb 14 '14 at 09:39
Kent, can you please help ? While i ran your command with the same as u given above i am getting error – Murali Feb 18 '14 at 17:13
/tmp>$ echo "P123456789,COLUMN2 > P123456790,COLUMN2 > P123456791,COLUMN2"|awk -F, -v OFS="," '{ "echo \""$1"\"|tr \"0-9\" \"5-9\"|tr \"A-Z\" \"X-Z\"" |getline $1}7' sh: -c: line 0: unexpected EOF while looking for matching `"' sh: -c: line 1: syntax error: unexpected end of file – Murali Feb 18 '14 at 17:14

score 1 · Answer 2 · answered Feb 13 '14 at 22:45

$ cat tst.awk
function tr(old,new,str,        oldA,newA,strA,i,j) {
    split(old,oldA,"")
    split(new,newA,"")
    split(str,strA,"")
    str = ""
    for (i=1;i in strA;i++) {
        for (j=1;(j in oldA) && !sub(oldA[j],newA[j],strA[i]);j++)
            ;
        str = str strA[i]
    }
    return str
}

BEGIN { FS=OFS="," }
{ print tr("P012345678","Z567899999",$1), $2 }

$ awk -f tst.awk file
Z678999999,COLUMN2
Z678999995,COLUMN2
Z678999996,COLUMN2

score 1 · Answer 3 · edited May 23 '17 at 12:29

1

Unfortunately, AWK does not have a built in translation function. You could write one like Ed Morton has done, but I would reach for (and highly recommend) a more powerful tool. Perl, for example, can process fields using the autosplit (-a) command switch:

-a turns on autosplit mode when used with a -n or -p. An implicit split command to the @F array is done as the first thing inside the implicit while loop produced by the -n or -p.

You can type perldoc perlrun for more details.

Here's my solution:

perl -F, -lane '$F[0] =~ tr/0-9/5-9/; $F[0] =~ tr/A-Z/X-Z/; print join (",", @F)' file.txt

Results:

Z678999999,COLUMN2
Z678999995,COLUMN2
Z678999996,COLUMN2

edited May 23 '17 at 12:29

Community

1
1

answered Feb 14 '14 at 01:38

Steve

51,466
13
89
103

1

Thanks Steve!!! Wonderful. I came to see that awk is running as C program so it would not allow to use unix commands, so i tried by system command for the translation part it worked for me. But i believe my way of approach would take processing time rather i will go by your approach – Murali Feb 14 '14 at 04:12
Even the below approach we can follow but depends on the processing speed we should choose the approach. >/tmp>$ awk 'BEGIN { FS=OFS="," } { $1=system("echo "$1"|tr '0-9' '5-9'|tr 'A-Z' 'X-Z'");$2="COLUMN2"} ' /tmp/file.txt >Result:=========== Z678999999 Z678999995 Z678999996 – Murali Feb 14 '14 at 04:16
1

@Murali: `system()` returns the command's _exit code_, not its stdout output; see @Kent's answer for capturing shell command output in a variable with `getline`. – mklement0 Feb 14 '14 at 04:25
@Murali: If you have the time, I'd be interested in seeing the benchmark of the three answers. My bet is that the Perl is the slowest, but the difference may only be negligible. – Steve Feb 14 '14 at 04:29

How to translate a column value in the file using awk with tr command in unix

Details:

3 Answers3

Linked