Joining Lines with join

If you've worked with databases, you'll probably know what to do with the UNIX join command; see your online manual page. If you don't have a database (as far as you know!), you'll still probably have a use for join: combining or "joining" two column-format files. join searches certain columns in the files; when it finds columns that match one another, it "glues the lines together" at that column. This is easiest to show with an example.

I needed to summarize the information in thousands of email messages under the MH mail system. MH made that easy: it has one command (scan) that gave me almost all the information I wanted about each message in the format I wanted. But I also had to use wc -l () to count the number of lines in each message. I ended up with two files, one with scan output and the other with wc output. One field in both lines was the message number; I used sort () to sort the files on that field. I used awk '{print $1 "," $2}' to massage wc output into comma-separated fields. Then I used join to "glue" the two lines together on the message-number field. (Next I fed the file to a PC running dBASE, but that's another story.)

Here's the file that I told scan to output. The columns (message number, email address, comment, name, and date sent) are separated with commas ():

andrewe@isc.uci.edu,,Andy Ernbaum,19901219 0002,bc3170x@cornell.bitnet,,Zoe Doan,19910104 0003,zcode!postman@uunet.uu.net,,Head Honcho,19910105 ...

Here's the file from wc and awk with the message number and number of lines:

0002,5 0003,187 ...

Then, this join command joined the two files at their first columns (-t, tells join that the fields are comma-separated):

% join -t, scanfile wcfile

The output file looked like:

andrewe@isc.uci.edu,,Andy Ernbaum,19901219,11 0002,bc3170x@cornell.bitnet,,Zoe Doan,19910104,5 0003,zcode!postman@uunet.uu.net,,Head Honcho,19910105,187 ...

Of course, join can do a lot more than this simple example shows. See your online manual page.

- JP