Help with joining two files

Shell Programming and Scripting
1

Greetings, all. I've got a project that requires I join two data files together, then do some processing and output. Everything must be done in a shell script, using standard unix tools. The files look like the following:

File_1
Layout:
Acct#,Subacct#,Descrip
Sample:
0001,0001,Account1/Sub1
0001,0002,Account1/Sub2
0002,0001,Account2/Sub1
0002,0002,Account2/Sub2
0002,0003,Account2/Sub3
...

File_2
Layout:
TransID,Code,Acct#,SubAcct#,Date,To,For,Amount,Ref#
Sample:
1,D,0002,0001,2006-01-03,Joe,Services,35.00,1234
2,C,0002,0003,2006-01-05,Mary,PC Repair,50.00,
3,D,0001,0001,2006-01-05,Amazon.com,book,39.95,1235
...

In essence, I need to add the proper description from File_1 into each record of File_2. Once the join is complete, I plan on using awk to summarize the data and output, so order is not important (description can go in right after the acct# & subacct# columns in File_2, or it can be appended to the end of each line).

I've tried join already, but since join expects to match on a single field, and I'm trying to match on two fields, join hasn't been much help. I've also tried sed, but I can't seem to get the replacement syntax right.

Any help would be greatly appreciated.

Rich Lohman

2

Try an adapt the following awk program :

BEGIN {
   FS = OFS = ",";
}
FNR == NR {
   Descr[$1,$2] = $3;
   next;
}
{
   if (($3,$4) in Descr) 
      print $0,Descr[$3,$4];
   else
      print $0,"Unknown Account";
}

Execute the program with the command :

awk -f program.awk File_1 File_2

The account description will be append to the end of each line of File_2.

Jean-Pierre.

3

That did the trick! Thanks Jean-Pierre!

4

Hi,

I'm failly new to UNIX scripting, but couldn't cat work?

I think cat file_1 file_2 > endfile will create a file that has file_1 and file_2 stuck together.

Or maybe grep the line you want, pipe it into another file and then use cat to join.

But then, I'm a newbie, so there's some glaring thing I might be missing.

Best wishes,
Laurel