Matching and Merging csv data fields based on a common field

Dear List,

I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this:

date,hostname,status,color,check
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change
02-03-2012,COMP1,PASS,Red,auth_pass_dictionary
02-03-2012,COMP1,PASS,Yellow,auth_pass_change
02-03-2012,SERVER2,PASS,Yellow,auth_pass_change
02-03-2012,SERVER2,FAIL,Yellow,auth_pass_change
02-03-2012,SERVER2,FAIL,Yellow,user_home_files

The second file has a unique field(field 2) being the hostname whereby the first field of this data is the customer_account as follows:

customer_account,hostname
CLIENT1,COMP1
CLIENT2,DESK3
CLIENT3,FIRE1
CLIENT4,SERVER2
cLIENT2,STATION1

What I am trying to achieve is match the hostname in the 2nd file with the hostname in the first file and merge the corresponding customer_account field from the second file into the first file as follows:

date,hostname,status,color,check,customer_account
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change,CLIENT1
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change,CLIENT1
02-03-2012,COMP1,PASS,Red,auth_pass_dictionary,CLIENT1
02-03-2012,COMP1,PASS,Yellow,auth_pass_change,CLIENT1
02-03-2012,SERVER2,PASS,Yellow,auth_pass_change,CLIENT4
02-03-2012,SERVER2,FAIL,Yellow,auth_pass_change,CLIENT4
02-03-2012,SERVER2,FAIL,Yellow,user_home_files,CLIENT4

Does anyone know of any solutions?

Thanks!

Land

awk 'NR==FNR{a[$2]=$1;next}{print $0","a[$2];}' FS="," file2 file1

Guru.

work perfectly, thanks guru!

Sorry to bring back this thread, this is exactly what i needed, with just a little change:

Same functionality as the above example, but i want to add "NONE" if no common field was found.

Thanks for the help