VLOOKUP utility in UNIX

Shell Programming and Scripting
1

Kindly help me to build the script similar to VLOOKUP function of MS Excell.

2

On what kind of data do you plan to act on? CSV?

3

Ordinary ASCII files with columns of data seperated by delimiter ; on each row. I am in desperate need & will be very thankful if you can provide solution.
Example
file TEST1 as
a1; b1; c1
a2; b2; c2
a3; b3; c3

file TEST2 as
a1; x1;
a3; x3
a4; x4

expected output
a1; x1; b1; c1
a2; ; b2;c2
a3; x3; b3; c3

thanks a lot in advance.

4

Try :

join -t ';' TEST2 TEST1

Jean-Pierre.

5

Thanks for reply. It works well for give example but not for my files probably because there are multiple mappings involved.
plz refer to file attached.

For example, for file test1 for first 3 rows (1st field "1"), it should be pick entry from test2 as "4" (because 1st entry of test1 i.e. 1 matches with 1st entry of test2 for all three cases.)

similarly for file test1 for 4th, 5th & 6th rows, the value picked up from file test2 should be "2".

plz help

Thanks a million in advance.

6

You can do something like that :

awk '
BEGIN {
   FS = OFS = ";";
}
NR==FNR {
   Values[$1+0] = $2;
   next;
}
{
   $1 = $1 OFS ($1+0 in Values ? Values[$1+0] : "");
   print $0;
}

' test2.txt test1.txt

Output:

1  ;4;1;1  ;2;
1  ;4;2;1  ;1;
1  ;4;3;1  ;1;
10 ;2;1;10 ;2;
10 ;2;2;10 ;3;
10 ;2;3;10 ;2;
12 ;4;1;12 ;3;
12 ;4;2;10 ;3;
12 ;4;3;12 ;1;
120;2;1;4  ;3;6
120;2;2;75 ;2;8
120;2;3;104;2;10

. . . . . . . . . . . 

77 ;4;3;   ; ;
8  ;4;1;   ; ;
8  ;4;2;   ; ;
8  ;4;3;   ; ;
9  ;4;1;   ; ;
9  ;4;2;   ; ;
9  ;4;3;   ; ;
79 ;4;1;   ; ;
79 ;4;2;   ; ;
79 ;4;3;   ; ;

Jean-Pierre.