If column matches another file, print every line with match (awk/grep)

Question

I’m taking two input files, one with certain ID numbers, and another with a large list of ID numbers and additional columns. The latter file contains multiple lines for each ID number and I need to extract all lines that match an ID from the first file. Those lines then must be printed in a new file.

Edit 1: Replaced sample files with excerpts from actual

Edit 2: Removed extra spaces that were in excerpt, but not actual file. Files likely need to be sanitized in some way, but how is unclear.

file1:

AT1G56430
AT3G55190
AT3G22880

file2:

AT1G01010|GO:0043090|RCA
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

expected output

AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

[[

I have tried:

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

and:

grep -Ff file2 file1 > output.txt

I’m aware that there are many somewhat similar questions posted in these forums and others. However, these don’t mention how the output is handled… nor do they mention duplicates. I’ve tried solutions from 4 of them, have been messing with this for many hours and keep getting the same problem: a blank output file.

I’m new to awk and I greatly appreciate the help. Sorry if this is a simple problem with syntax etc; please let me know. Thanks for the help.

Answer 1

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

Answer 2

give a try this awk command.

bash-4.1$ cat file1
1
3

bash-4.1$ cat file2
1|A|B
1|C|D
2|E|F
3|G|H

bash-4.1$ awk -F\| 'NR==FNR{F1[$0];next}$1 in F1{print}' file1 file2
1|A|B
1|C|D
3|G|H

Answer 3

Have you tried join already?

$ tail -n +1 -- file1 file2
==> file1 <==
AT1G56430
AT3G55190
AT3G22880

==> file2 <==
AT1G01010|GO:0043090|RCA
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

$ join -t'|' file1 file2
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI