UNIX for Advanced & Expert Users

Below is the scenario. Help is appreciated.

File1: ( 500,000 lines ) : Three fields comma delimited : Not sorted

1234FAA,435612,88975
1224FAB,12345,212356


File2: ( 4,000,000 lines ) : Six fields comma delimited (Last 3 field should match the 3 fields of File1) : Not Sorted :

0123456abcd,12345,abcdef,1234FAA,435612,88975
0123456wxyz,11234,lmnopq,1224FAB,12345,212356

I need to grab all the six fields for file2 when there is a match of first 3 fields of file1 and last 3 fields of file2.

I wrote a small script but seems like it might take days to complete :-)

1 #!/bin/ksh
2
3 while read record
4 do
5 cat file2 | grep "$record" >> final.list
6 done < file1

Can someone help me with a faster solution?

Thanks in advance.

Code:

egrep -f file1 file2

nawk -F',' -f hem.awk file1 file2

hem.awk:

Code:

FNR==NR { f1[$0]; next}
( $(NF-2) FS $(NF-1) FS $(NF) ) in f1

vgersh99,

Linux box does not have nawk.
Has awk and gawk.

'gawk' is your new friend.

vgersh99,

The following ran for about 45-50 minutes and completed, but the output file was empty.
Could you please give me more insight on what is happening below?

Thanks

gawk -F',' -f hem.awk file1 file2 > final.list

hem.awk:
FNR==NR { f1[$0]; next}
( $(NF-2) FS $(NF-1) FS $(NF) ) in file1

ShellLife,

The egrep command ran over an hour and killed it.
I kicked it off again to see how long it runs.

Thanks

hemangjani,
based on your sample input files and the proposed awk script, the output came out as expected. I'v also modified the 'file2' file to add non=matching records found in file1, and the result was as expected.

I believe your actual files are not the same as the ones you'v quoted above: there might be inconsistent spaces/tabs between the fields and/or some other anomalities you're not paying attention to that result in the 'empty' output.

I'd suggest copy/pasting the part of the content of files file1 and file2 here using the Vb Codes so that the formating does not 'get lost in translation' [pun intended].