Matching 2 files based on key

UNIX for Beginners Q & A
1

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

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

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

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:

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

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

awk 'NR==FNR{a[substr($0,1,8)];next} ($2 in a)'   file2 file1 >> output
4 
grep -Ff file2 file1
5

You're not comparing the same thing, try

awk 'NR==FNR{a[substr($0,1,8)];next} (substr($2,1,8) in a)'   file2 file1
1 Like
6 
grep -f <(sed 's/.*/^&/' file2) file1
1 Like