Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help require for vLookup Utility same as in EXCEL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help require for vLookup Utility same as in EXCEL
# 1  
Old 01-04-2013
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
Code:
1
2
11
4
12

and File2.txt is
Code:
1 tab1
2 tab2
3 tab3
4 tab4
5 tab5

then the utility should create File3.txt with the below mentioned output:
Code:
1 tab1
2 tab2
11
4 tab4
12

the separator between columns will be tab(\t).

Also, is it possible, if File1.txt and File2.txt has multiple columns then, the lookup should happen from Column 3 of File1.txt to Column 6 of File2.txt and return Column 9 in File3.txt

Please help
Last edited by Scrutinizer; 01-04-2013 at 10:05 AM.. Reason: code tags
# 2  
Old 01-04-2013
From the above examples you could try:-

Code:
#!/bin/ksh
while read f1
do
   f2line=`grep "^${f1}tab" File2.txt`
   f2="${f2line#*tab}"
   echo "${f1}tab${f2}"
done < File1.txt > File3.txt

That might get you started, but the follow-up question is a bit harder.


Robin
Liverpool/Blackburn
UK

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For the follow up question, consider:-

Code:
#!/bin/ksh
cut -f3 -d "tab" File1.txt | while read f1
do
   f2line=`cut -f6,9 -d "tab" File2.txt|grep "^${f1}tab"
   f2="${f2line#*tab}"
   echo "${f1}tab${f2}"
done > File3.txt


Does that do the job?

These may be a little slow as they are running a while read loop. Perhaps someone can suggest a good awk that will run faster. You will notice the difference with larger files as that may read the files once where my suggestion will re-read File2.txt for every line in File1.txt

Robin
Last edited by rbatte1; 01-04-2013 at 10:10 AM..
This User Gave Thanks to rbatte1 For This Post:
viveklgupta007
# 3  
Old 01-04-2013
For your first request, try
Code:
$ awk 'FNR==NR{Ar[$1]=$0;next} $1 in Ar {$0=Ar[$1]}1' FS="\t" OFS="\t" file2 file1
1    tab1
2    tab2
11
4    tab4
12

For your second, pls post samples, as I don't quite understand it.
This User Gave Thanks to RudiC For This Post:
viveklgupta007
# 4  
Old 01-04-2013
Thans a ton Rudi.....this works...

For 2nd request, please find below the scenario:

File1.txt:
Code:
nu1    eu11    10
nu2    eu12    2
nu3    eu13    14
nu4    eu14    4
nu5    eu15    9
nu6    eu16    0
nu7    eu17    7
nu8    eu18    12
nu9    eu19    9
nu10    eu20    1

File2.txt:
Code:
ea1    1    dc1    ek1    vivek1
ea2    2    dc2    ek2    vivek2
ea3    11    dc3    ek3    vivek3
ea4    4    dc4    ek4    vivek4
ea5    5    dc5    ek5    vivek5
ea6    6    dc6    ek6    vivek6
ea7    7    dc7    ek7    vivek7
ea8    12    dc8    ek8    vivek8
ea9    9    dc9    ek9    vivek9
ea10    10    dc10    ek10    vivek10

Expected Output, File3.txt:
Code:
nu1    eu11    10    vivek10
nu2    eu12    2    vivek2
nu3    eu13    14    
nu4    eu14    4    vivek4
nu5    eu15    9    vivek9
nu6    eu16    0    
nu7    eu17    7    vivek7
nu8    eu18    12    vivek8
nu9    eu19    9    vivek9
nu10    eu20    1    vivek1


Lookup 3rd column in File1.txt against 2nd column in File2.txt and vlookup the value of 5th column in the output against the same

Please refer attachment fpr sample files

Thanks
File1.txt (122 Bytes) 
File2.txt (225 Bytes) 
File3.txt (183 Bytes) 
Last edited by Franklin52; 01-04-2013 at 12:51 PM.. Reason: Incorrect Sample Provided; mod: code tags
# 5  
Old 01-04-2013
I think your formatting has gone a bit wild when you've pasted the file contents in. Could you have another go. Can you wrap them with Code Tags too.

Does my follow up code above work perchance? If not, how badly is it wrong?




Robin

---------- Post updated at 03:17 PM ---------- Previous update was at 03:15 PM ----------

Oops, crossed postings. I will have a look-see.


Robin

---------- Post updated at 03:23 PM ---------- Previous update was at 03:17 PM ----------

Syntax error and then no matching output. I will think again.


Sorry.

---------- Post updated at 03:30 PM ---------- Previous update was at 03:23 PM ----------

Syntax error corrected, but no hits. Your input file File2.txt does not have anything in columns 6 & 9 to work with.

My code is now:-
Code:
cut -f3 -d "tab" File1.txt | while read f1
do
   f2line=`cut -f6,9 -d "tab" File2.txt|grep "^${f1}tab"`
   f2="${f2line#*tab}"
   echo "${f1}tab${f2}"
done > File3.txt

... and seems to work when I add more text on each line in File2.txt


Have I mis-interpreted your needs?


Robin
# 6  
Old 01-04-2013
Hi Robin...

i am getting the following error while executing the second code...
Code:
line 4: unexpected EOF while looking for matching ``'
line 8: syntax error: unexpected end of file

pls help

---------- Post updated at 09:02 PM ---------- Previous update was at 09:01 PM ----------

hi robin,

there is something wrong going in the formatting...can you please download the attached files for samples...

thanks

---------- Post updated at 09:05 PM ---------- Previous update was at 09:02 PM ----------

im getting this error:
Code:
line 6: syntax error near unexpected token `done'
line 6: `done > File3.txt'

Last edited by Franklin52; 01-04-2013 at 12:52 PM.. Reason: code tags
# 7  
Old 01-04-2013
I will attach my script just in case it's the copy/paste through the thread.



Robin
File.sh (179 Bytes) 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Vlookup in Linux

Hello Everybody I am looking for vlookup like functionality in Linux since two files I have are very big(1000MB each) and its not opening completely in excel. Here the requirement file1 11,12,13 16,14,12 28,21,22 22,23,24 file 3 18,16,16 14,12,12 23,22,24 16,11,13 here... (8 Replies)
Discussion started by: shanul karim
8 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

Vlookup using awk

Hi folks, awk 'NR==FNR {m=$0; next} $1 in m{$0=m} {print}' file2 file1 Works a charm for a vlookup type query, sourced from https://www.unix.com/shell-programming-and-scripting/215998-vlookup-using-awk.html However my column content has white spaces and numbers. Example file1 The Man... (6 Replies)
Discussion started by: pshields1984
6 Replies

5. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

6. Shell Programming and Scripting

Vlookup using awk

Hello, I am trying to use vlookup (Excel function) using awk but there is some problem :( --file1-- ABC123 101F X1 A $P=Z X2 A $P=X X3 B $P=F X4 C $P=G MNK180 END --file2-- X1 A_t $P=Z X2 A_t $P=X X3 B_u $P=F X4 C_o $P=G (2 Replies)
Discussion started by: young
2 Replies

7. Shell Programming and Scripting

How to Unzip a file using unzip utility for files zipped without zip utility ?

Hi, I need to zip/compress a data file and send to a vendor. The vendor does have only unzip utility and can accept only .ZIP files. I do not have zip utility in my server. How do I zip/compress the file so that it can be deflated using unzip command ? I tried gzip & compress commands, but... (1 Reply)
Discussion started by: Sabari Nath S
1 Replies

8. Shell Programming and Scripting

PERL: Split Excel Workbook to Indiv Excel files

Hi, I am trying to find a way to read an excel work book with multiple worksheets. And write each worksheet into a new excel file using perl. My environment is Unix. For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create... (2 Replies)
Discussion started by: sandeep78
2 Replies

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

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