Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Want to implement VLOOKUP (Excel function) in Unix

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Want to implement VLOOKUP (Excel function) in Unix
# 1  
Old 05-14-2008
MySQL 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:
Code:
MSC	             Cell	SDCA
Patna-1	             12	Bihar
Patna-2	             45	Ranchi
Bhopal-1	             85	Raigarh
Bhopal-2	             66	Indore
Mumbai-1	78	Turbhe
Mumbai-2	25	Nashik
Mumbai-3	45	Dhule

& File2:
Code:
MSC		New MSC
Patna-1		Patna1
Patna-2		Patnaomp2
Bhopal-1	             bhopal
Bhopal-2	             bhopal2
Mumbai-1	mum1
Mumbai-2	mumomp2
Mumbai-3	mum3

The required output must like this:
Code:
result
MSC		Cell	SDCA
Patna1		12	Bihar
Patnaomp2	45	Ranchi
bhopal		85	Raigarh
bhopal2		66	Indore
mum1		78	Turbhe
mumomp2	25	Nashik
mum3		45	Dhule

Please help me for the same.

Thank u
Last edited by Yogesh Sawant; 05-14-2008 at 02:18 AM.. Reason: added code tags
# 2  
Old 05-14-2008
Try...
Code:
awk 'NR==FNR {a[$1]=$2;next} a[$1] {$1=a[$1]} {print}' file2 file1 > file3

# 3  
Old 05-14-2008
You are looking for the join command. It's pesky in that it requires sorted input, so these forums are shock full of examples of doing it without sorting the input for a particular file format. Scaling up, if you have a lot of these, you might want to import them into a proper database.

Code:
awk 'NR==FNR { m[$1] = $2; next }
{ $1 = m[$1]; print }' file2 file1

Searching for NR==FNR should get you a few handfuls of examples, as well as the typical follow-up questions and problems.
# 4  
Old 05-14-2008
Thanks.. it solve my problem
Thanks.. it solve my problem
# 5  
Old 05-14-2008
Dear era... small doubt
After exceuting your code it is giving me out in different format. out file is given below. I want the out in same line.. can u please help??

Output file.
file1
New MSC
Patna1
Patnaomp2
bhopal
bhopal2
mum1
mumomp2
mum3
Cell SDCA
12 Bihar
45 Ranchi
85 Raigarh
66 Indore
78 Turbhe

& required format is
MSC Cell SDCA
Patna1 12 Bihar
Patnaomp2 45 Ranchi
bhopal 85 Raigarh
bhopal2 66 Indore
mum1 78 Turbhe
mumomp2 25 Nashik
mum3 45 Dhule

Pls help....
# 6  
Old 05-14-2008
Worked for me here, did you copy+paste correctly? Does Ygor's code work better for you?
# 7  
Old 05-25-2008
Dear Quote,
This code is showing errors

awk: syntax error near line 1
awk: bailing out near line 1
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. 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

3. Shell Programming and Scripting

Excel vlookup function like value mapping with awk

I have two files File1 175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 179967 st_001256606.1 rs93516.2... (1 Reply)
Discussion started by: sammy777888
1 Replies

4. Shell Programming and Scripting

Trying to implement count_collatz_step function

Can anyone give me a clue why this code doesn't work as expected? The function count_collatz_step() take one parameter which a number that need to calculate collatz until it reaches 1. The func should return steps it takes. # # Count steps of collatz conjecture takes, until the number reach 1.... (8 Replies)
Discussion started by: Bunchhieng
8 Replies

5. UNIX for Dummies Questions & Answers

Command for vlookup function

Hello experts, I have large text files that need to be arranged using a function like excel's vlookup. I have been playing with awk command but didn't really come up with a solution. Could anyone please help me out? Below are my datasets and expected output. Any help would be greatly... (8 Replies)
Discussion started by: makelifeeasier
8 Replies

6. 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

7. UNIX for Dummies Questions & Answers

Implement the '&&' function in a shell

Hello, I'm currently implementing the && function in a shell using C. For example, if we input cmd1 && cmd2, then cmd2 executes only when cmd1 exits successfully. I'm thinking about: int main() { int i; char **args; while(1) { printf("yongfeng's shell:~$ "); args =... (5 Replies)
Discussion started by: Yongfeng
5 Replies

8. 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

9. Programming

How to implement polling for a function using timer in C?

Hi, Can you please help me in implementing a timer based polling for function in C? ie. the function should be called in say 30secs(when 30secs has lapsed). Thanks (7 Replies)
Discussion started by: naan
7 Replies

10. Shell Programming and Scripting

VLOOKUP utility in UNIX

Kindly help me to build the script similar to VLOOKUP function of MS Excell. (5 Replies)
Discussion started by: hjoshi
5 Replies
Login or Register to Ask a Question