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:
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
#!/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:-
```text
#!/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
Very odd. It works fine for me on AIX 4.3.3, 5.1 (yes I know they're pretty old!) & 6.1 and also on RH Linux 6. Did you save the attachment and FTP it to the server, or copy & paste the contents?
i saved the content into another text file, renamed it as .sh and then executed it on Windows-machine through Cygwin...i am using cygwin as i do not have any linux machine to execute...
Could I direct you to look at the Cygwin home page here
The section that states what it isn't makes me worry that we're trying to do something that Cygwin is not designed for. You might well be better with a DOS batch file, an Excel Macro or some Visual Basic code etc.
If anyone wants to leap in and correct me, feel free as I am happy to learn.
Me2....however i am using Cygwin for all my shell scripts and it is working fine....let me see if i can get a linux server from my friend...or will ask him to test their...whether it works...
alternately if someone has some suggestion to this problem....pls WELCOME..
also if someone can help me with a different code for my second request....that would be great...
@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.
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...
This is working as it should be...THANKS A TON....
---------- 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....?
With a small tweak, I have the following for putting the whole of the line from File1.txt in the output:-
#!/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?
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
