Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.
File 1:
Connect|20130320000023|UTC|PPP|test3@test3.co.uk|test1@test1.co.uk|0BCBE578|
File 2:
Connect|20130320000023|UTC|PPP|test1@test1.co.uk|test1@test1.co.uk|0BCBE578|
Connect|20130320000025|UTC|PPP|test2@test2.co.uk|test1@test1.co.uk|0BCBE578|
Connect|20130320000025|UTC|PPP|test3@test3.co.uk|test1@test1.co.uk|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|test3@test3.co.uk|test1@test1.co.uk|0BCBE578|

Many thanks,

share|improve this question
    
In other words, print lines from file2 whose 5th column is found in at least one line of file1 at the 5th column. Correct? I'm asking this because the second column in the matching records doesn't match and you chose to print the file2 version. –  Joseph R. Oct 14 '13 at 13:32

3 Answers 3

Method #1: grep & awk

You can use this snippet to do it:

$ grep -f <(awk -F '|' '{print $5}' file1)  file2
Connect|20130320000025|UTC|PPP|test3@test3.co.uk|test1@test1.co.uk|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|test3@test3.co.uk|test1@test1.co.uk|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/||.*//'
test3@test3.co.uk|Connect|20130320000025|UTC|PPP|test1@test1.co.uk|0BCBE578
share|improve this answer
1  
+1 Careful, though. This will print lines from file2 containing the data in the 5th column of file1 anywhere. –  Joseph R. Oct 14 '13 at 13:29
    
@JosephR. - thanks, I'm working out a more explicit version that isn't as loose on the matching as this one. –  slm Oct 14 '13 at 13:43
    
@JosephR. - I've added the venerable FNR==NR method using awk too. –  slm Oct 14 '13 at 13:53
    
Yeah, I'll have to read about this one some time. I see it a lot in answers around here. –  Joseph R. Oct 14 '13 at 13:55
1  
@JosephR. - Good example of awk's looping constructs: unixcl.com/2009/01/difference-between-awk-nr-and-fnr.html –  slm Oct 14 '13 at 14:10

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|test3@test3.co.uk|test1@test1.co.uk|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 
share|improve this answer

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.

share|improve this answer
    
a solution using comm would be the most efficient but the setup of it can be a bit of a pain, so as long as the data sets we're dealing with are small (100k lines or less, even more than that isn't really an issue either, but drawing a line in the sand) using awk FNR==NR is fine. –  slm Oct 14 '13 at 15:02
    
perhaps you could show an example? Sort of put your money where your mouth is. 8-) We typically show how on this site, not just mention potential solutions. You'll find some issues when dealing with join as we've had in the past with questions of this nature. unix.stackexchange.com/questions/90214/… –  slm Oct 14 '13 at 15:12
    
see my answer for an implementation of the join/sort method. –  slm Oct 14 '13 at 15:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.