Merging CSV fields based on a common field

Shell Programming and Scripting
1

Hi List,

I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So:

File 1:

dbid,uuid,account_code,hostname,os,Support_group_name,location
123,aa79-11df-829d,BMC,bnehws2,Microsoft Windows,Server Ops,UNKNOWN
345,9b7a-11e0-9810,AAA,psmpd01o,Microsoft Windows,UNKNOWN,UNKNOWN
789,fe63-11df-98bd,CCS,hnam01,Red Hat Linux,unix support,Sydney
124,072c-11e0-8171,CCS,syd71,Microsoft Windows,Windows support,INDIA

I want to match the account_code field with the Org Code in File 2:

Org Code,Account Name,Org ID,IntID,Region,Status,Comments
BMC,BMC Corp,111,752654929,AUS,Active,
AAA,AAA Corp,104,750335008,AUS,Active,
CCS,CCS Corp,185,758011030,EMEA,Active,
DDD,DDD Corp,992,756512445,EMEA,Active,

And then merge the matched Account Name(field 2) into the data in File1 without omitting any records in File1, so output data looks like this:

dbid,uuid,account_code,hostname,os,Support_group_name,location
123,aa79-11df-829d,BMC,bnehws2,Microsoft Windows,Server Ops,UNKNOWN,BMC Corp
345,9b7a-11e0-9810,AAA,psmpd01o,Microsoft Windows,UNKNOWN,UNKNOWN,AAA Corp
789,fe63-11df-98bd,CCS,hnam01,Red Hat Linux,unix support,Sydney,CCS Corp
124,072c-11e0-8171,CCS,syd71,Microsoft Windows,Windows support,INDIA,CCS Corp

Any suggestions much appreciated..

Thanks,
Land

2 
perl -F, -ane '($.==1)&&print;
chomp ($line = $_); open I, "< file2";
for (<I>) {
    @x = split /,/;
    if ($F[2] eq $x[0]) {
        print "$line,$x[1]\n";
    }
}' file1
1 Like