Find matches for the 5th columns in a pipe delim files

Question

File 1:
Connect|20130320000023|UTC|PPP|[email protected]|[email protected]|0BCBE578|
File 2:
Connect|20130320000023|UTC|PPP|[email protected]|[email protected]|0BCBE578|
Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|
Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|

I need to find matching records in both File 1 and File 2 in column 5. So from the above I need to return in the output:

Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|

Many thanks,

Answer 1

Method #1: grep & awk

You can use this snippet to do it:

$ grep -f <(awk -F '|' '{print $5}' file1)  file2
Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|

Details

The bit that uses awk parses the first file, file1 pulling out all the 5th columns. These values are then used as a list to grep, which will print any lines in the 2nd file that contain a match.

Caveats with this method

This method will match any occurrence of the 5th column from file1 in file2.

Method #2: Just awk

Another approach that has been used on the site in the past is to use awk's FNR facility. This is where awk will iterate over 2 files, going through the second file line by line, for each line in the first.

An approach like this would do it. Put the following into a file, cmds.awk:

FNR == NR {
f1[$5] = $5
next
}

{ if ($5 == f1[$5]) print $0; }

You can then run this as follows:

$ awk -F '|' -f cmds.awk file1 file2

NOTE: You could've used this awk pattern instead:

FNR == NR {
f1[$5] = $5
next
}

{ if ($5 in f1) print $0; }

Example

$ awk -F '|' -f s.awk file1 file2
Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|

Caveats with this method

This approach can only handle a single instance of each email address from file1. So if there are 2 lines that both have the same value for the 5th column, this won't be able to distinguish between them. This seems acceptable given your requirements in the OP though.

Join & sort

You can also do this using join and sort.

$ join -t '|' -j 5  <(sort -k5,5 file2) <(sort -k5,5 file1) | sed 's/||.*//'

This will use the separator | and join the sorted files on the 5th column. This approach prints the matches from both file1 and file2, so we use sed to chop the 2nd match off the end.

Example

$ join -t '|' -j 5  <(sort -k5,5 file2) <(sort -k5,5 file1) | sed 's/||.*//'
[email protected]|Connect|20130320000025|UTC|PPP|[email protected]|0BCBE578

Answer 2

I would do the whole thing in perl:

$ perl -F'\|' -ane '$k{$F[4]}++; print if $k{$F[4]}>1' file1 file2  
Connect|20130320000025|UTC|PPP|[email protected]|[email protected]|0BCBE578|

• -a activates automatic field splitting into the array @F.
• -F'\|' sets the field delimiter for -a to |.
• For each line processed, save the 5th field (array indices start at 0 in perl) as a hash key ($k{$F[4]}++ and increment its value by one. The second time a field is seen, that value will be 2.
• The script will process each line of both files (file1 before file2) and print the line if the 5th field has been seen before, i.e. if $k{$F[4]} is greater than one.

This assumes that no fifth column is repeated within the same file. If this is not the case and some columns may be duplicated in the same file, use this instead:

perl -e 'open(A,"$ARGV[0]"); while(<A>){@F=split(/\|/);$k{$F[4]}++;}
         open(B,"$ARGV[1]"); while(<B>){@F=split(/\|/); print if $k{$F[4]} 
         }' file1 file2 

Answer 3

If the file sizes are comparable, the optimal solution is to sort both files by the column you are interested in and then join them by that column. If the file sizes are N and M then the asymptotic run time is O(N*log(N)+M*log(M)).

If one of the files is much much smaller than the other, then the O(N*M) solution in the other answers is better.