Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Lookup file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Lookup file
# 1  
Old 05-10-2012
Lookup file
Hi,

need your help to lookup these 2 files

main.txt
Code:
RNPMS01,PMS717W_Marasi,CXP9016141/1_R7G04,EXECUTING
RNPMS01,RAP765W_BakaranBatu,CXP9014346/1_R6AG03,EXECUTING
RNPMS01,RNPMS01,CXP9014711/2_R5Z,EXECUTING
RNPMS01,TBT510W_Bandar_Utama,CXP9014346/1_R6AG03,EXECUTING
RNPMS01,PMS209W_Simpang_Dua,CXP9016868/1_R4G02,EXECUTING
RNPMS01,PMS207W_Ade_Irma,CXP9014346/1_R6AG03,EXECUTING
RNPMS01,PMS224W_Suzuya_Siantar,CXP9014346/1_R6AG03,EXECUTING
RNPBO01,PBO043W_KTI_Probolingo,CXP9014346/1_R6AG03,EXECUTING

code.txt
Code:
CXP9016141/1_R7G04,P5.1.2
CXP9014346/1_R6AG03,W10.1.3.7 
CXP9014711/2_R5Z,P7.1.4
CXP9016868/1_R4G02,P6.1.2.3

Expected output:
Code:
RNPMS01,TBT510W_Bandar_Utama,W10.1.3.7,EXECUTING

Thanks

BR
///Singgih


Moderator's Comments:
Mod Comment Please use next time code tags for your code and data
Last edited by Corona688; 05-10-2012 at 01:53 PM..
# 2  
Old 05-10-2012
Can you describe how you arrive at this output? I imagine replace 3rd field of main.txt with 2nd field of code.txt, but your output shows 1 line and there are many matches for "CXP9014346/1_R6AG03"

Code:
[mute@geek ~/temp/singgih]$ ./script code.txt main.txt
RNPMS01,PMS717W_Marasi,P5.1.2,EXECUTING
RNPMS01,RAP765W_BakaranBatu,W10.1.3.7,EXECUTING
RNPMS01,RNPMS01,P7.1.4,EXECUTING
RNPMS01,TBT510W_Bandar_Utama,W10.1.3.7,EXECUTING
RNPMS01,PMS209W_Simpang_Dua,P6.1.2.3,EXECUTING
RNPMS01,PMS207W_Ade_Irma,W10.1.3.7,EXECUTING
RNPMS01,PMS224W_Suzuya_Siantar,W10.1.3.7,EXECUTING
RNPBO01,PBO043W_KTI_Probolingo,W10.1.3.7,EXECUTING

is that correct?
# 3  
Old 05-11-2012
Quote:
Originally Posted by neutronscott
Can you describe how you arrive at this output? I imagine replace 3rd field of main.txt with 2nd field of code.txt, but your output shows 1 line and there are many matches for "CXP9014346/1_R6AG03"

Code:
[mute@geek ~/temp/singgih]$ ./script code.txt main.txt
RNPMS01,PMS717W_Marasi,P5.1.2,EXECUTING
RNPMS01,RAP765W_BakaranBatu,W10.1.3.7,EXECUTING
RNPMS01,RNPMS01,P7.1.4,EXECUTING
RNPMS01,TBT510W_Bandar_Utama,W10.1.3.7,EXECUTING
RNPMS01,PMS209W_Simpang_Dua,P6.1.2.3,EXECUTING
RNPMS01,PMS207W_Ade_Irma,W10.1.3.7,EXECUTING
RNPMS01,PMS224W_Suzuya_Siantar,W10.1.3.7,EXECUTING
RNPBO01,PBO043W_KTI_Probolingo,W10.1.3.7,EXECUTING

is that correct?

Yes, that's expected output
Can you show me the script?

Thanks

BR
///Singgih
# 4  
Old 05-11-2012
Oh yes, sorry. I suppose I could have posted it while awaiting confirmation.

Code:
#!/usr/bin/awk -f
BEGIN { FS=OFS="," }
FNR==NR { a[$1]=$2; next }
($3 in a) { print $1, $2, a[$3], $4 }

Is standard awk lookup. FNR==NR rule reads in file1, placing column 2 into an array (keyed with column 1).

Also as 1-liner: awk -F, 'FNR==NR{a[$1]=$2;next}($3 in a){print $1,$2,a[$3],$4}' OFS=, code.txt main.txt
This User Gave Thanks to neutronscott For This Post:
singgih
# 5  
Old 05-11-2012
Quote:
Originally Posted by neutronscott
Oh yes, sorry. I suppose I could have posted it while awaiting confirmation.

Code:
#!/usr/bin/awk -f
BEGIN { FS=OFS="," }
FNR==NR { a[$1]=$2; next }
($3 in a) { print $1, $2, a[$3], $4 }

Is standard awk lookup. FNR==NR rule reads in file1, placing column 2 into an array (keyed with column 1).

Also as 1-liner: awk -F, 'FNR==NR{a[$1]=$2;next}($3 in a){print $1,$2,a[$3],$4}' OFS=, code.txt main.txt
Hi neutronscott,

Million thanks,
can you explain meaning of this:
Code:
FNR==NR { a[$1]=$2; next }

BR
///Singgih
# 6  
Old 05-11-2012
I thought I did? Smilie NR is internal variable which holds the record number. So basically is the line number in this case. FNR is per-file, so if they're equal you're currently processing a line in the first file. the action { a[$1]=$2; next } first assigns an array using the first field as the key. the next means go on to the next line without further processing (skips the remaining print block).

so we're at the 2nd file and FNR==NR is no longer true. (imagine the first file is just 4 lines as your example, the first line in the second file FNR would be 1 while NR would continue to 5). In that case we check if the 3rd field matches any of the stored elements ($3 in a) and print...
This User Gave Thanks to neutronscott For This Post:
singgih
# 7  
Old 05-14-2012
Thank you for explanation of the script.

The above script will give the print out all lines in file2 that 'match' with record in file1. Is possible to print unmatched line? expected print out for unmatched record as below example, if CXP9016868/1_R5G08 is not in the record:
Code:
RNPMS01,PMS209W_Simpang_Dua,CXP9016868/1_R5G08,UNKNOWN

looking forward for reply
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Lookup value from another file

Hi Experts, I need you help in coding the below scenario in unix. Please help Lets say i have below 2 records in my file1 a;b;c a;d and in file 2 , i have the below information a:alpha b:beta c:code d:delta then i want to lookup on file2 and replace my content in file... (2 Replies)
Discussion started by: ashishagg2005
2 Replies

2. Shell Programming and Scripting

Lookup Within a file

A text file has two logs of same event & both logs have to be correlated into a single line before same can be processed further RAW OPERATING LOG Date Month Year Time Event 10 JAN 2014 1000 4 11 APR 2013 1230 2 12 FEB 2014 ... (7 Replies)
Discussion started by: newageBATMAN
7 Replies

3. Shell Programming and Scripting

Lookup name from another file

Hi All, I want to lookup name for an id in col2 input from another file and add the name to each line. Input 1 comp100001_c0_seq1 At1g31340 30.40 569 384 11 3 1673 313 834 7e-62 237 comp100003_c0_seq1 At1g35370_2 35.00 80 50 ... (7 Replies)
Discussion started by: gina.lizar
7 Replies

4. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files: 1) Third field from file1.txt should be compared to the first field of lookup.txt. 2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt. 3)Else just print the line from file1.txt. File1.txt:... (4 Replies)
Discussion started by: venalla_shine
4 Replies

5. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies

6. Shell Programming and Scripting

Lookup file

I have two files ,File1 lookup to file2 based on 1st&3rd against 1st and 2nd column and produce the following o/p File1 450000|USD|USD 450006|GKSGD|SGD 450002|XSGD|SGD File2 ----- 450000|USD|2000.00|10000.000 450006|SGD|1000.200|3000.000 450002|SGD|3000.000|20000.00 O/p ... (4 Replies)
Discussion started by: mohan705
4 Replies

7. Shell Programming and Scripting

Lookup on a file

Hi, I have the following requirement. I have one lookup file which contains 15 columns and 7000 records. Ex: 123,MEDICA,134,145,1178,123,678,345,2345,HP,COL,K12,SR,OX,78919 I have input file which contains 14 columns and 20 million records.Some times the record count is more... (4 Replies)
Discussion started by: ukatru
4 Replies

8. UNIX for Advanced & Expert Users

Lookup on a file

Hi, I have the following requirement. I have one lookup file which contains 15 columns and 7000 records. Ex: 123,MEDICA,134,145,1178,123,678,345,2345,HP,COL,K12,SR,OX,78919 I have input file which contains 14 columns and 20 million records.Some times the record count is more than 20... (1 Reply)
Discussion started by: ukatru
1 Replies

9. UNIX for Dummies Questions & Answers

Lookup with a file

Hi All, i have a variable which has a value in it. RETAILER='JEWL' i have a text file. Name: file.txt file.txt ________ WLG 150 JEWL 60 CVS 240 FLN 120 WND 120 I am trying to write a korn script.the script, based on the value in the RETAILER will do a look up against the... (5 Replies)
Discussion started by: pavan_test
5 Replies

10. UNIX for Dummies Questions & Answers

file lookup

I need to do a text lookup for multiple records against a file that contains a key and two results. I found this code: str=`awk '$1 == search' search=$1 lkup.tbl It's a little tricky because the first $1 is the key variables location in the lkup.tbl and the second $1 is the parameter passed... (6 Replies)
Discussion started by: gillbates
6 Replies
Login or Register to Ask a Question