Replace fileds with AWK by using a different file as translation list

Question

I am using awk in Windows. I have a script called test.awk. This script should read a file and replace a certain filed (key) with a value. The key->value list is in a file called translate.txt.

It's structure is like this:

e;Emil    
f;Friedrich
g;Gustaf
h;Heinrich
i;Ida

In a simple example, my input file would be

e,111    
f,222
g,333
h,444
i,555
..

so the output should be

Emil,111
Friedrich,222
Gustaf,333
Heinrich,444
Ida,555
..

The script I an having is using a user function key2value to do the replacement, but I don't succeed in giving this function another file translate.txt as a source. See my code:

{   
    FS=","
    d=key2value($1)
    print d "," $2
}

function key2value(b)
{
    #this should use another file, not the currently processed one
    FILENAME="translate.txt"  

begin
{
    FS=";"

    if ($1=b)
    { 
       return $2
    }

end 

}

Another thing, the FS is buggy to, it starts working from the second line only.

Answer 1

This simple one-liner will do the trick:

awk  'FNR==NR{a[$1]=$2;next}{print a[$1],$2}' FS=',|;' OFS=',' translate input
Emil,111
Friedrich,222
Gustaf,333
Heinrich,444
Ida,555

In script form:

BEGIN {                # The BEGIN block is executed before the files are read
    FS="[,;]"          # Set the FS to be either a comma or semi-colon
    OFS=","            # Set the OFS (output field separator) to be a comma
}
FNR==NR {              # FNR==NR only true when reading the first file
   key2value[$1]=$2;   # Create associative array of key,value pairs 
   next                # Grab the next line in the first file
} 
{                      # Now in the second file, print looked up value and $2 
    print key2value[$1],$2
}

Run like:

awk -f translate.awk translate.txt input.txt

---

There are numerous error with your script, you should take a read of Effective AWK Programming.

Answer 2

Code for GNU sed (Windows quoting):

sed -r "s#(\S+);(\S+)#/^\1,/s/.*,(\\S+)/\2,\\1/#" file1|sed -rf - file2

Shell session:

>type file1 file2

file1


e;Emil
f;Friedrich
g;Gustaf
h;Heinrich
i;Ida

file2


e,111
f,222
g,333
h,444
i,555

>sed -r "s#(\S+);(\S+)#/^\1,/s/.*,(\\S+)/\2,\\1/#" file1|sed -rf - file2
Emil,111
Friedrich,222
Gustaf,333
Heinrich,444
Ida,555