How to Use Grep/Awk/Unix to Match All Lines from One File in Another File

awkgawkgrep

I'm trying to retrieve the the matching lines from a file, however the file that has the "index" or list of matches to retrieve also has duplicate entries and I want to print out the duplicates as well.

Example:

File 1 (index file or list of strings to match)

abc
tgf
abc
ggg
aaa
fff

File 2 (what I want to retrieve for each of the matches in file 1)

Fred   1   3   abc
Amy    2   4   ggg
Dan    5   6   tgf
Mike   9   1   fff
Steve  2   1   aaa

My desired result would be (order is not important, just that I match every line):

Fred   1   3   abc
Dan    5   6   tgf
Fred   1   3   abc
Amy    2   4   ggg
Steve  2   1   aaa
Mike   9   1   fff

I'm familiar and have tried grep as follows, but it does not print the duplicate entries.

grep -Fwf file1.txt file2.txt

Fred   1   3   abc
Amy    2   4   ggg
Dan    5   6   tgf
Mike   9   1   fff
Steve  2   1   aaa

Best Answer

Via awk

awk 'NR==FNR{A[$4]=$0;next}{print A[$1]}' file2.txt file1.txt

Or sorted output via join:

join -o 2.1 2.2 2.3 2.4 -2 4 <(sort file1.txt) <(sort -k4 file2.txt)

Related Solutions

AWK/GREP – Print Every Line with Match if Column Matches Another File

Your AWK script is nearly there:

awk -F'|' 'NR==FNR{c[$1]++;next};c[$1] > 0' file1 file2 > output.txt

works, after changing the line-endings from Mac to Unix:

tr '\r' '\n' < file1 > file1.new
mv file1.new file1
tr '\r' '\n' < file2 > file2.new
mv file2.new file2

$1 is the first field in AWK.

Instead of c[$1] > 0, you can write c[$1]. The > 0 isn't needed: any non-zero value works, so we might as well use the contents of c directly:

awk -F'|' 'NR==FNR{c[$1]++;next};c[$1]' file1 file2 > output.txt

Compare two files and print matches – large files

If the files are sorted (the samples you posted are) then it's as simple as

join -t : File1.txt File2.txt

join pairs up lines from two files where the join field is equal. By default, the join field is the first field, the fields are output in order except that the join field is not repeated, and non-pairable lines are skipped, which is exactly what you want.

Note that if the files have Windows line endings, they appear under Unix systems to have an extra carriage return character at the end of each line. The CR is mostly visually invisible, but as far as join and other text tools are concerned, it's a character like any one else, and it means the fields of File1.txt all end with a CR whereas the ones in File2.txt don't so they don't match. You need to strip the CR, at least in File1.txt.

<File1.txt tr -d '\r' | join -t : - File2.txt

You do need to sort the files. If they aren't, then ksh/bash/zsh, you can use process substitutions. (Add tr -d '\r' | if needed.)

join -t : <(sort File1.txt) <(sort File2.txt)

In plain sh, if your Unix variant has /dev/fd (most do), you can use that instead to pipe the output of two programs through two file descriptors.

sort File2.txt | { sort File1.txt | join -t : /dev/fd/0 /dev/fd/3; } 3<&1

If you need to preserve the original order of File1.txt and it isn't sorted by the join field, then add line numbers to remember the original order, sort by the join field, join, sort by line numbers and strip the line numbers. (You can do something similar if you want to preserver the order of the other file.)

<File1.txt nl -s : |
sort -t : -k 2 |
join -t : -1 2 - <(sort File2.txt) |
sort -t : -k 2,2n |
cut -d : -f 1,3

Best Answer

Related Solutions

AWK/GREP – Print Every Line with Match if Column Matches Another File

Compare two files and print matches – large files

Related Question