Append a field to the end of each line of a file based on searching another file.

Hi All,

I have two comma separated value(CSV) files, say FileA and FileB.
The contents looks like that shown below.

FileA
EmpNo,Name,Age,Sex,
1000,ABC,23,M,
1001,DES,24,F,
1002,JHS,26,F,
1003,JOS,42,M,
...................

FileB
EmpNo,Spouse,
1000,DEB,
1002,FAR,
................

FileA has say 20000 lines and FileB has 1000. What I am trying to do is to append the spouse's name in the FileA if the employee is married. Else I need to append a N.A., or something. So for each EmpNo in FileA, I need to check FileB for a matching first column, and if a match is found append FileA with second column of FileB. Else Append a N.A. to the end of that line.

So my output file should be something like the one shown below.

FileC
EmpNo,Name,Age,Sex,Spouse,
1000,ABC,23,M,DEB,
1001,DES,24,F,N.A.,
1002,JHS,26,F,FAR,
1003,JOS,42,M,N.A.,

I know this can be done using awk or sed. But I am not much familier with them either. Please help me with some pointers to tackle this.. Some sample codes are most welcome..

Thanks in Advance.
Ultimate.

nawk -f ult.awk FileB FileA

here's ult.awk:

BEGIN { FS=OFS="," }
FNR == NR { arr[$1]=$2; next }
{ $NF=($1 in arr) ? arr[$1] : "N.A."; print }

Thanks a lot vgersh99!!
This exactly satisfies the need.. Now i realize that i know nothing about awk.. :rolleyes:

I have been trying for half a day to accomplish this.. Great help..

Btw, I did not have nawk, and tried with awk and it works fine.. Is there any big difference b/w both?