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
# 15  
Old 01-04-2013
@vivekgupta: use vi and you will see the problem: ^M characters
correct it with dos2unix command

@rbatte1: the forums think the world is a PC even though we are UNIX. When I copied and pasted in UNIX, voila! DOS carriage returns. Your code is just fine - assuming the OP actually cut and pasted it and removed ^M characters.
This User Gave Thanks to jim mcnamara For This Post:
viveklgupta007
# 16  
Old 01-05-2013
Thanks JIM.....it did really helped.....thanks a ton !!! Smilie

---------- Post updated at 11:49 PM ---------- Previous update was at 11:43 PM ----------

THANKS A TON FOR ALL OF YOU HELPING ME RESOLVE THIS PROBLEM...

RUDI

JIM

ROBIN


THANKS A TON.... Smilie Smilie Smilie Smilie Smilie Smilie Smilie

---------- Post updated 01-05-13 at 09:54 AM ---------- Previous update was 01-04-13 at 11:49 PM ----------

Hi Robin,

the output that i am getting frm ur script my second request is:
Code:
10    vivek10
2    vivek2
14    
4    vivek4
9    vivek9
0    
7    vivek7
12    vivek8
9    vivek9
1    vivek1

I need the output in the below mentioned format, the contents of File1.txt should be as it is, only the last column should be appended to the output file File3.txt with the looked up value...

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


can u pls look into this and help...

thanks...
Last edited by radoulov; 01-07-2013 at 07:49 AM..
# 17  
Old 01-05-2013
Sorry for my late reply - you can adapt my earlier proposal easily. Try
Code:
$ awk 'FNR==NR{Ar[$2]=$5;next} $3 in Ar {$0=$0 FS Ar[$3]}1' FS="\t" OFS="\t" file2 file1
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

This User Gave Thanks to RudiC For This Post:
viveklgupta007
# 18  
Old 01-07-2013
This is working as it should be...THANKS A TON.... Smilie

---------- Post updated at 05:57 PM ---------- Previous update was at 05:14 PM ----------

Hi Rudi....with the current code we would be able to fetch single column value....if we need to fetch multiple column for both the requirement, how do we go about it....can you please help....?
# 19  
Old 01-07-2013
With a small tweak, I have the following for putting the whole of the line from File1.txt in the output:-
Code:
#!/bin/ksh

while read f1line
do
   f1="${f1line##*tab}"
   f2line=`cut -f6,9 -d "tab" File2.txt|grep "^${f1}tab"`
   f2="${f2line#*tab}"
   echo "${f1line}tab${f2}"
done < File1.txt > File3b.txt

Is that better?


Robin

---------- Post updated at 12:51 PM ---------- Previous update was at 12:48 PM ----------

Sorry, I'd been away from my desk with the reply all keyed, so I pressed submit to find a follow up!

Okay, well, it depends what multiple column values you are after. If it's easy to say that columns 2 & 3 in File1.txt must match columns 6 & 7 in File2.txt then we could work on that. Can you describe the rules you need applied?



Thanks,
Robin
Last edited by rbatte1; 01-07-2013 at 10:59 AM.. Reason: Spelling and grammar
# 20  
Old 01-07-2013
so File1.txt is:
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 is:
Code:
ea1    1    dc1    ek1    vivek1    vv1    ww1
ea2    2    dc2    ek2    vivek2    vv2    ww2
ea3    11    dc3    ek3    vivek3    vv3    ww3
ea4    4    dc4    ek4    vivek4    vv4    ww4
ea5    5    dc5    ek5    vivek5    vv5    ww5
ea6    6    dc6    ek6    vivek6    vv6    ww6
ea7    7    dc7    ek7    vivek7    vv7    ww7
ea8    12    dc8    ek8    vivek8    vv8    ww8
ea9    9    dc9    ek9    vivek9    vv9    ww9
ea10    10    dc10    ek10    vivek10    vv10    ww10

the output should be File3.txt:
Code:
nu1    eu11    10    vivek10    ww10    kk10
nu2    eu12    2    vivek2    ww2    kk2
nu3    eu13    14            
nu4    eu14    4    vivek4    ww4    kk4
nu5    eu15    9    vivek9    ww9    kk9
nu6    eu16    0            
nu7    eu17    7    vivek7    ww7    kk7
nu8    eu18    12    vivek8    ww8    kk8
nu9    eu19    9    vivek9    ww9    kk9
nu10    eu20    1    vivek1    ww1    kk1


Please find attached the files for sample and reference...

So what we did earlier is lookup column 3 in File1.txt in Column 2 in File2.txt and extracted Column 5 from File2.txt...now on same condition extract Column5 and Column 7 in the output file...so multiple column to be extracted instead of one from File2.txt and the output should be as available in the attached File3.txt
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
File2.txt (307 Bytes) 
File3.txt (253 Bytes) 
Last edited by vgersh99; 01-07-2013 at 10:35 AM.. Reason: code tags, please!
# 21  
Old 01-07-2013
Sorry for missing what I'm looking at, but I thought the code added the line from File1.txt with column 9 from File2.txt when column 3 from File1.txt matched column 6 on File2.

Anything is possible if we can define the rules, but I'm also stuck as to where to get value kk10 etc. from.

Sorry about that.


Robin
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