Matching lines across multiple csv files and merging a particular field

Shell Programming and Scripting
1

I have about 20 CSV's that all look like this:

"","[fname]","[lname]","[prefix]","[suffix]","[fax]","[phone]","[business]","[address1]","[address2]","[city]","[state]","[zip]","[setdate]","[email_type]","[start_code]"

What I've been told I need to produce is the exact same thing, but with each file now containing the start_code from every other file where the email matches.

It doesn't matter if any of the other fields don't match, just the email field is important, and the only change to each file would be to add any other start_code values from other files where the email matches.

For example, if the same email appeared in the wicq.csv, oota.csv, and itos.csv it would go from being the following in each file:

"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"OOTA"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ITOS"

to

"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX, OOTA, ITOS"

for all three files (wicq.csv, oota.csv, and itos.csv)

I've done some googling, but haven't found anything yet. Any help is appreciated, even if it's just what the proper terms I should be searching for are.

2

Hi,

if you have three files file1, file2 and file3 with content like:

file1:

"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ALBI QUAZ"

file2:

"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"OOTA"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"TUZZI"

file3:

"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ITOS"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"PANZ"

awk -F, '{if (! a[$1]){for (i=1;i<NF;i++){s=s $i FS}a[$1]=s FS $NF;s=""} \
    else{a[$1]=a[$1] $NF}}\
    END{for (i in a){gsub(/""/,", ",a); print a}}' file1 file2 file3

You will get:

"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,,"ALBI QUAZ, TUZZI, PANZ"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,,"WIQC PDX, OOTA, ITOS"

HTH Chris