Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

VLOOKUP utility in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting VLOOKUP utility in UNIX
# 1  
Old 10-30-2007
VLOOKUP utility in UNIX
Kindly help me to build the script similar to VLOOKUP function of MS Excell.
# 2  
Old 10-30-2007
On what kind of data do you plan to act on? CSV?
# 3  
Old 10-31-2007
files type
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  
Old 10-31-2007
Try :
Code:
join -t ';' TEST2 TEST1

Jean-Pierre.
# 5  
Old 10-31-2007
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.
test1.txt (53.4 KB) 
test2.txt (4.0 KB) 
# 6  
Old 10-31-2007
You can do something like that :
Code:
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:
Code:
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to compare two files in UNIX using similar to vlookup?

Hi, I want to compare same column in two files, if values match then display the column or display "NA". Ex : File 1 : 123 abc xyz pqr File 2: 122 aab fdf pqr fff qqq rrr (1 Reply)
Discussion started by: hkoshekay
1 Replies

2. Linux

UNIX Utility Development Conventions?

I'm slowly hacking away at a zsh script that shows some promise as a command line tool. I want to learn more about the conventions regarding command line tool development in Unix (and/or macOS), but don't really know where to look for this information. What is the correct way, or convention, to... (2 Replies)
Discussion started by: MonilGomes
2 Replies

3. UNIX for Beginners Questions & Answers

Vlookup on 2 files - inserting vlookup command on another command

Hello, i am trying to print group name column(etc/group) on script (etc/passwd) since group name is not listed on etc/passwd columns. Im trying to do a vlookup. but i cant figure out how i can insert the vlookup command FNR==NR inside the print out command or the output. I also tried exporting... (2 Replies)
Discussion started by: joonisio
2 Replies

4. UNIX for Beginners Questions & Answers

UNIX mail utility

Hi, I work on a Middleware application which is installed on Windows, but its file System is on HP UNIX. I am supposed to change the SMTP host address for my application. But I am not able to find the file where the old SMTP host would have been configured/hardcoded. Can anyone assist me on this... (2 Replies)
Discussion started by: jhilmil
2 Replies

5. Shell Programming and Scripting

awk script to perform an action similar to vlookup between two csv files in UNIX

Hi, I am new to awk/unix and am trying to put together an awk script to perform an action similar to vlookup between the two csv files. Here are the contents of the two files: File 1: Date,ParentID,Number,Area,Volume,Dimensions 2014-01-01,ABC,247,83430.33,857.84,8110.76... (9 Replies)
Discussion started by: Prit Siv
9 Replies

6. Shell Programming and Scripting

Help require for vLookup Utility same as in EXCEL

Hi guys, can someone please help me with a vlookup utility in shell script that does the work similar to Excel. Ex: If File1.txt is 1 2 11 4 12 and File2.txt is 1 tab1 2 tab2 3 tab3 4 tab4 5 tab5 then the utility should create File3.txt with the below mentioned output: (24 Replies)
Discussion started by: viveklgupta007
24 Replies

7. UNIX for Dummies Questions & Answers

age utility in unix

Hi, Can anyone explain me the use of age utility and how it works? Looking forward reply... Thanks, Venkatesh. (1 Reply)
Discussion started by: venkatesht
1 Replies

8. Shell Programming and Scripting

Want to implement VLOOKUP (Excel function) in Unix

Dear All, i want to implement vookup function which is there in excel into Unix. Suppose i have 2 files. The files are given below. File1: MSC Cell SDCA Patna-1 12 Bihar Patna-2 45 Ranchi Bhopal-1 85 Raigarh Bhopal-2 ... (8 Replies)
Discussion started by: pravani1
8 Replies

9. SCO

Zip utility in sco unix

how can zip a directory in sco unix and transfer it to any other system using ftp. and finally how can i unzip it regards, ajay (1 Reply)
Discussion started by: ajay234
1 Replies

10. UNIX for Advanced & Expert Users

Problem with tip utility in unix

Hi , I have wrote a telnet clinet application to interact with remote system . This program takes the screen shots for every interaction and send back to us. After connecting to remote machine , i want to call tip utility to interact with a device which is connected to one remote system. Now my... (0 Replies)
Discussion started by: prasadvsda
0 Replies
Login or Register to Ask a Question