Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to extract all lines from file2 that begin with an id # prefix contained in file1.

File 1 is single column like:


File 2 is multi-column like:

1 [tab] 108 [tab] Anarchist [tab] 103985
324 [tab] 309 [tab] Melodies [tab] 230498

What's the quickest easiest way to extract just these lines from File2?

share|improve this question
up vote 1 down vote accepted
$ while read p; do awk '$1 == "'$p'"' file2; done < file1


$ awk -F'\t' 'FNR==NR { a[$0]; next } $1 in a' file1 file2
  • FNR: the number of records read from the current file being processed
  • NR: total number of input records
  • FNR==NR: is only true when awk is reading the file1
  • a[$0]: create an array element indexed by $0 (from file1)
  • $1 in a: check whether each line being read from file2 exists as an index in the array a
share|improve this answer
I tried this first, but received this error: awk: program limit exceeded: maximum number of fields size=32767 FILENAME="file2" FNR=10788 NR=10790 – Poe Oct 23 '11 at 0:18
How many columns in file2? – quanta Oct 23 '11 at 0:37
file2 is a 28G file, 11 columns – Poe Oct 23 '11 at 3:18
missing the FS declaration: `awk -F '\t' '...' file1 file2 – glenn jackman Oct 23 '11 at 3:59
@Poe, with that much data, you should be using a real database. – glenn jackman Oct 23 '11 at 4:02

bash code to do this:

for i in $(cat file1); do egrep "^$i\s" file2; done
share|improve this answer
i'm more familiar with grep, so this worked i just went with grep -m 1 -P '$i\t' – Poe Oct 23 '11 at 0:30

This is probably the fastest:

grep -f <( sed 's/.*/^&\t/' file1) file2

The answers using for and while loops are going to be very slow.

The awk answer by quanta should work. I don't know why it wouldn't unless your line endings are non-Unix or file1 is very big.

share|improve this answer
the other method is definitely slow, but the file is very big. – Poe Oct 23 '11 at 3:19

1) We can use some OR-logic of grep. For example

$> grep -P "^(324|399|408|135236|321590).*" file2
324 [tab] 309 [tab] Melodies [tab] 230498

So question is - how we can get this variable to grep?

2) We can echo file1 in single line and substitute delimiters with |, than add brackets.

$> echo `cat file1` | sed -r -e 's/([0-9])\ ([0-9])/\1,\2/g'

So, finally we have a variant without a for-while loops.

grep -P "^($( echo `cat file1` | sed -r -e 's/([0-9])\ ([0-9])/\1|\2/g'  )).*" file2
share|improve this answer
echo ``cat file1`` | sed ... -- really? How about sed -r -e '...' file1 – glenn jackman Oct 23 '11 at 3:58
@glennjackman: "echo file1 in single line" – Dennis Williamson Oct 24 '11 at 1:20

The join command GNU coreutils server just this purpose, but it is picky about its input.

$ sort file1 > sorted1
$ sort file2 > sorted2
$ join -t"      " sorted1 sorted2 | sort -n

The join command requires its input files to be sorted lexicographically, not numerically. Thus, all that sorting of the inputs and the output.

To specify that the output from join should be tab delimited, use -t"tab character", which you would type as Ctrl-V Tab at the Bash prompt.

share|improve this answer

Your Answer


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.