Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Matching 2 files based on key

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Matching 2 files based on key
# 1  
Old 02-14-2019
Matching 2 files based on key
Hi all

I have two files I need to match record from first file and second file on column 1,8 and and output only match records on file1

Code:
File1:
020059801803180116130926800002090000800231000245204003160000000002000461OUNCE000000350000100152500BM01007W0000                              0508000005260005280
020059801804180123130926800091100033500231000245204003160000000002000461OUNCE000000350000100152550IL01007W0000                                 0508000003150003140
020070371802180107130928900058520000000231001026901000790100000006000480OUNCE000000350000100029550OO10007W0000                              0508000055100000000
020078421803180119130900000001810077700231001202701002790000000002000461OUNCE000000350000400012610UB19020W0000                              0508000016360023390
020144001803180116130927700076970000000231000140501000670000000006000461OUNCE000000350000100023350OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231000140701000670000000006000462OUNCE000000350000100000750OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231000245101000670000000006000461OUNCE000000350000100071750OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231000245201000670000000006000461OUNCE000000350000100152550OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231000567501000670000000006000461OUNCE000000350000100029450OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231001076001000670000000006000461OUNCE000000350000100027250OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231001083101000670000000006000487OUNCE000000350000100023850OO08060W0000                              0508000003010002890
020144001803180116130927700076970000000231003358301000670000000006000475OUNCE000000350000100175050OO08060W0000                              0508000003010002890
020164721802180111130926800049140662500231001083105005950100000006000487OUNCE000000350000100023860VS01066W0000                              05080000142900168400
020167611801171231130925400069200191200231001199921014700000000002000461OUNCE000000350000100017360CS08007W0000                              0508000028520021880
020167611801180101130926000002090004400231001051502001580000000002000480OUNCE000000350000100159000BM08007W0000                              0508000019020024500
020167611801180101130926000002090004400231001157002001580000000002000487OUNCE000000350000100071700BM08007W0000                              0508000019020024500
020213491804180121130926917369200085600231000140602001400000000002000462OUNCE000000350000100038760CS02014W0000                              0508000010340011970
020213491804180121130926917369200085600231001026902001400000000002000480OUNCE000000350000100029560CS02014W0000                              0508000010340011970
020213491804180121130926917369200085600231001027002001400000000002000480OUNCE000000350000100156560CS02014W0000                              0508000010340011970
020213491804180121130926917369200085600231001051502001400000000002000480OUNCE000000350000100159060CS02014W0000                              0508000010340011970
020213491804180121130926917369200085600231003358102001400000000002000475OUNCE000000350000100163960CS02014W0000                              0508000010340011970
020213491804180121130926917369200085600231003358202001400000000002000475OUNCE000000350000100090360CS02014W0000                              0508000010340011970

File 2:
0200598018031801161309
0200598018041801231309
0200703718021801071309
0200784218031801191309


Output:
020059801803180116130926800002090000800231000245204003160000000002000461OUNCE000000350000100152500BM01007W0000                              0508000005260005280
020059801804180123130926800091100033500231000245204003160000000002000461OUNCE000000350000100152550IL01007W0000                                 0508000003150003140
020070371802180107130928900058520000000231001026901000790100000006000480OUNCE000000350000100029550OO10007W0000                              0508000055100000000
020078421803180119130900000001810077700231001202701002790000000002000461OUNCE000000350000400012610UB19020W0000                              0508000016360023390


I tried and it is not getting me right values

Code:
awk 'NR==FNR{a[$1];next} ($1 in a)' file_1.txt file_2.txt > output.txt

# 2  
Old 02-14-2019
You're giving it file_1 first, which isn't the file containing the keys to match with, and later, matching against column 1 of the other wrong file.

So, let's try saving the keys from file_2, then matching them with the second column of file_1:

Code:
awk 'NR==FNR{a[$1];next} ($2 in a)' file_2.txt file_1.txt > output.txt

This User Gave Thanks to Corona688 For This Post:
arunkumar_mca
# 3  
Old 02-14-2019
I want to match only 1,8 position and match the record. Is there a way I can add in thew awk ? . I tried the below and it is not getting me any output

Code:
awk 'NR==FNR{a[substr($0,1,8)];next} ($2 in a)'   file2 file1 >> output

Last edited by arunkumar_mca; 02-14-2019 at 04:10 PM.. Reason: updated command
# 4  
Old 02-14-2019
Code:
grep -Ff file2 file1

# 5  
Old 02-14-2019
Quote:
Originally Posted by arunkumar_mca
I want to match only 1,8 position and match the record. Is there a way I can add in thew awk ? . I tried the below and it is not getting me any output

Code:
awk 'NR==FNR{a[substr($0,1,8)];next} ($2 in a)'   file2 file1 >> output

You're not comparing the same thing, try
Code:
awk 'NR==FNR{a[substr($0,1,8)];next} (substr($2,1,8) in a)'   file2 file1

This User Gave Thanks to Corona688 For This Post:
arunkumar_mca
# 6  
Old 02-14-2019
Code:
grep -f <(sed 's/.*/^&/' file2) file1

This User Gave Thanks to nezabudka For This Post:
arunkumar_mca
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Match tab-delimited files based on key

I thought I had this figured out but was wrong so am humbly asking for help. The task is to add an additional column to FILE 1 based on records in FILE 2. The key is in COLUMN 1 for FILE 1 and in COLUMN 1 OR COLUMN 2 for FILE 2. I want to add the third column from FILE 2 to the beginning of... (8 Replies)
Discussion started by: andmal
8 Replies

2. Shell Programming and Scripting

Files summary using awk based on index key

Hello , I have several files which are looking similar to : file01.txt keyA001 350 X string001 value001 keyA001 450 X string002 value007 keyA001 454 X string002 value004 keyA001 500 X string003 value005 keyA001 255 X string004 value006 keyA001 388 X string005 value008 keyA001 1278 X... (4 Replies)
Discussion started by: alex2005
4 Replies

3. Shell Programming and Scripting

awk - Merge two files based on one key

Hi, I am struggling with the an awk command to merge two files based on a common key. I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended File1 COL1|COL2|COL3|COL4|COL5|COL6|COL7... (3 Replies)
Discussion started by: Ads89
3 Replies

4. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

5. UNIX for Dummies Questions & Answers

How to fetch files right below based on some matching criteria?

I have a requirement where in i need to select records right below the search criteria qwertykeyboard white 10 20 30 30 40 50 60 70 80 qwertykeyboard black 40 50 60 70 90 100 qwertykeyboard and white are headers separated by a tab. when i execute my script..i would be searching... (4 Replies)
Discussion started by: vinnu10
4 Replies

6. Shell Programming and Scripting

Merge two files based on a 3rd key file

Hi, I want to merge the two files based on the key file's columns. The key file: DATE~DATE HOUSE~IN_HOUSE CUST~IN_CUST PRODUCT~PRODUCT ADDRESS~CUST_ADDR BASIS_POINTS~BASIS_POINTS ... The other 2 files are From_file & To_file - The From_file: DATE|date/time|29|9 ... (9 Replies)
Discussion started by: dips_ag
9 Replies

7. Shell Programming and Scripting

Matching 2 files based on one column

Hi, On a similar subject, the following. I have two files: file1.txt dbSNP_rsID,Chromosome,Position,Gene rs10399749,chr. 01,45162,? rs4030303,chr. 01,72434,? rs4030300,chr. 01,72515,? rs940550,chr. 01,78032,? rs13328714,chr. 01,81468,? rs11490937,chr. 01,222077,? rs6683466,chr.... (5 Replies)
Discussion started by: swvanderlaan
5 Replies

8. Shell Programming and Scripting

joining files based on key column

Hi I have to join two files based on 1st column where 4th column of a2.txt=at and take 2nd column of a1.txt and 3rd column of a2.txt and check against source files ,if matches list those source file names. a1.txt a1|20090809|20090810 a2|20090907|20090908 a2.txt a1|d|file1.txt|at... (9 Replies)
Discussion started by: akil
9 Replies

9. Shell Programming and Scripting

Merge files based on key

Hi Friends, Can any one help me with merging these file based on two columns : File1: A|123|99|SAMS B|456|95|GEORGE D|789|85|HOVARD File2: S|123|99|NANcY|6357 S|123|99|GREGRO|83748 A|456|95|HARRY|827|somers S|456|95|ANTONY|546841|RUDOLPH|7263 B|456|95|SMITH|827|BOISE STATE|834... (3 Replies)
Discussion started by: sbasetty
3 Replies

10. Shell Programming and Scripting

merging two files based on some key

I have to merge two files: The files are having the same format like A0this is first line TOlast line silmilarly other lines. I have to search for A0 line in the second file also and then put the data in the third file under A0 heading ,then for A1 and so on. A0 portion will be treminated... (1 Reply)
Discussion started by: Vandana Yadav
1 Replies
Login or Register to Ask a Question