Help to Create this file format structure

Shell Programming and Scripting
1

This data comes form the table and exported into the file in this format

File1 Format
weboffercode1,sourcecode1,1,1,1,1,1,1

weboffercode1,sourcecode2,1,1,1,1,1,1

weboffercode1,sourcecode1,1,1,1,1,1,1

weboffercode1,sourcecode3,1,1,1,1,1,1

weboffercode1,sourcecode3,1,1,1,1,1,1

weboffercode2,sourcecode1,1,1,1,1,1,1

weboffercode2,sourcecode1,1,1,1,1,1,1

weboffercode2,sourcecode2,1,1,1,1,1,1

weboffercode3,sourcecode1,1,1,1,1,1,1

weboffercode3,sourcecode2,1,1,1,1,1,1

I have to format this file in the following format as :-

File2 :
sum of the field f3,f4,f5,f6,f7,f8 for same WebOfferCode and Source code combination

weboffercode1,sourcecode1,2,2,2,2,2,2

weboffercode1,sourcecode2,1,1,1,1,1,1

weboffercode1,sourcecode3,2,2,2,2,2,2

sum of fields f3,f4,f5,f6,f7,f8 for same WebOfferCode

TOTAL, weboffercode1,5,5,5,5,5,5

sum of the field f3,f4,f5,f6,f7,f8 for same WebOfferCode and Source code combination

weboffercode2,sourcecode1,2,2,2,2,2,2

weboffercode2,sourcecode2,1,1,1,1,1,1

sum of fields f3,f4,f5,f6,f7,f8 for same WebOfferCode

TOTAL, weboffercode1,3,3,3,3,3,3

sum of the field f3,f4,f5,f6,f7,f8 for same WebOfferCode and Source code combination

weboffercode3,sourcecode1,1,1,1,1,1,1

weboffercode3,sourcecode2,1,1,1,1,1,1

sum of fields f3,f4,f5,f6,f7,f8 for same WebOfferCode
TOTAL, weboffercode2,2,2,2,2,2,2

So that if I save this file2.csv it is in the format of the picture as shown above.Let me know how can I do the operations on File1 to create such file format as File2...Primarily I was to know how to write a script to add the data present in the file column wise.

2

can't you just do it all in excel - i imagine excel is much better at maths than most shell script languages

3

Something like that ?

sort -t, -k1,2 inputfile | \
awk -F, '

function new_sourcecode(scode) {
   if (ssum3 != "")
      printf("%s,%s,%d,%d,%d,%d,%d,%d\n",weboffercode,sourcecode,
                                         ssum3,ssum4,ssum5,ssum6,ssum7,ssum8);
   ssum3 = ssum4 = ssum5 = ssum6 = ssum7 = ssum8 = 0;
   sourcecode = scode;
}

function new_weboffercode(wcode) {
   if (wsum3 != "")
      printf("TOTAL,%s,%d,%d,%d,%d,%d,%d\n",weboffercode,
                                            wsum3,wsum4,wsum5,wsum6,wsum7,wsum8);
   wsum3 = wsum4 = wsum5 = wsum6 = wsum7 = wsum8 = 0;
   weboffercode = wcode;
}
{
   if ($1 != weboffercode) {
      new_sourcecode($2);
      new_weboffercode($1);
   } else if ($2 != sourcecode) {
      new_sourcecode($2);
   }

   ssum3 += $3; wsum3 += $3;
   ssum4 += $4; wsum4 += $4;
   ssum5 += $5; wsum5 += $5;
   ssum6 += $6; wsum6 += $6;
   ssum7 += $7; wsum7 += $7;
   ssum8 += $8; wsum8 += $8;
}
END {
   new_sourcecode("");
   new_weboffercode("");
}
'

Output:

weboffercode1,sourcecode1,2,2,2,2,2,2
weboffercode1,sourcecode2,1,1,1,1,1,1
weboffercode1,sourcecode3,2,2,2,2,2,2
TOTAL,weboffercode1,5,5,5,5,5,5
weboffercode2,sourcecode1,2,2,2,2,2,2
weboffercode2,sourcecode2,1,1,1,1,1,1
TOTAL,weboffercode2,3,3,3,3,3,3
weboffercode3,sourcecode1,1,1,1,1,1,1
weboffercode3,sourcecode2,1,1,1,1,1,1
TOTAL,weboffercode3,2,2,2,2,2,2

Jean-Pierre.

4

Well i tried running it worked..thanks!!1:)

5 
nawk 'BEGIN{FS=","}
{
a[$1$2]=$3+a[$1$2]
b[$1$2]=$4+b[$1$2]
c[$1$2]=$5+c[$1$2]
d[$1$2]=$6+d[$1$2]
e[$1$2]=$7+e[$1$2]
f[$1$2]=$8+f[$1$2]
A[$1]=$3+A[$1]
B[$1]=$3+B[$1]
C[$1]=$3+C[$1]
D[$1]=$3+D[$1]
E[$1]=$3+E[$1]
F[$1]=$3+F[$1]
if(t[$1]=="")
	t[$1]=$2
else if (index(t[$1],$2)==0)
	t[$1]=sprintf("%s,%s",t[$1],$2)
}
END{
for (i in A)
{
	tt=split(t,temp,",")
	for (j=1;j<=tt;j++)
	{
		p=sprintf("%s%s",i,temp[j])
		print i","p","a[p]","b[p]","c[p]","d[p]","e[p]","f[p]
	}
	print "TOTAL,"i","A","B","C","D","E","F
}
}' filename