Help to Create this file format structure


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help to Create this file format structure
# 1  
Old 04-22-2008
Help to Create this file format structure

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  
Old 04-22-2008
hmmm...

can't you just do it all in excel - i imagine excel is much better at maths than most shell script languages
# 3  
Old 04-22-2008
Something like that ?
Code:
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:
Code:
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  
Old 04-25-2008
Thanks a lot it worked....

Well i tried running it worked..thanks!!1Smilie
# 5  
Old 04-29-2008
Code:
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[i],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[i]","B[i]","C[i]","D[i]","E[i]","F[i]
}
}' filename

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Create movie from jpg (or other picture format) file

hi linux expert is there any program for create movie file from pictures file (like jpg)? Many Thanks samad (1 Reply)
Discussion started by: abdossamad2003
1 Replies

2. Shell Programming and Scripting

Create a flat file and directory structure

Hi All, is there any work around to generate the file and directory structure like below at left side at Output? and exclude all file except .abc .txt Current Directory structure |-------------files |---------------Share |-----------------dir1 |-----------------dir2... (11 Replies)
Discussion started by: heros
11 Replies

3. Shell Programming and Scripting

Needed touch command to create a file in the following format

needed touch command to create a file in the following format touch a_yyyymmdd_hhmmss (1 Reply)
Discussion started by: hemanthsaikumar
1 Replies

4. Shell Programming and Scripting

Create Directory structure

Hello ; ) again Now I have my file like this : DIR2/DIR3 DIR2 DIR2/DIR3/DIR4/DIR5 I am looking for help to create a loop that will create the directory structure. I need something like this : If "DIR2" does not exist > Create IF "DIR2" exist already > check if onther "DIR"... (5 Replies)
Discussion started by: Aswex
5 Replies

5. Shell Programming and Scripting

How we can create the master file through shell to show the tree structure of the directory?

Can we create the master file that show the whole tree structure of the directory till a particular folder? Database that contains four sub repository Sybase,sql,oracle,mysql and sql and oracle contains two subrepostories Siebel and plsql and each repositories contains three folders... (1 Reply)
Discussion started by: rohit22hamirpur
1 Replies

6. Shell Programming and Scripting

How to create a C structure using table description.

There is table 'DEPT' in the database with the following desciption: Name Null? Type ------- -------- ------------------------ DEPTNO NOT NULL NUMBER(2) DNAME NULL VARCHAR2(14) LOC NULL VARCHAR2(13) Using shell script, I need to create a structure for the... (2 Replies)
Discussion started by: ehari
2 Replies

7. UNIX for Advanced & Expert Users

to create format file for bcp in

I want to create the format file for bcp in a file to the table. My Versions are: Unix: Sun OS, DB: Sybase. How can i create the format file. Is there any way to create the format by bcp command or some system sp. Please help. (3 Replies)
Discussion started by: Arunprasad
3 Replies

8. Shell Programming and Scripting

How to format or create a matrix report from file

Dear Unix champs, I have a input file as attached, i would like to create an report from the file as below FileType | EQUENS0001 | EQUENS0002 | EQUENS1100 | EQUENS0003 --------+-------------------------------------------------------- Msg No |... (3 Replies)
Discussion started by: manas_ranjan
3 Replies

9. Shell Programming and Scripting

How to create a directory structure with getting input from a file.

Hi How to create a directory structure with getting input from a file. I have file in that following lines are written. ./activemq-4.1.2/activemq-core-4.1.2.jar ./activemq-4.1.2/backport-util-concurrent-2.1.jar ./camel-1.4.0/apache-camel-1.4.0.jar ./camel-1.4.0/lib/activation-1.1.jar... (12 Replies)
Discussion started by: itsjoy2u
12 Replies

10. UNIX for Dummies Questions & Answers

How to create concatenate a file in tab format

Hi, Do you know given a file which is SED at the same time, how to read it and AWK it in tabs format. Means:- I have an input file which is (at the same time perform sed):- 1 2 3 4 5 6 7 I would like to print out in tab format which is:- 1 2 3 4 5 6 7 .... (12 Replies)
Discussion started by: ahjiefreak
12 Replies
Login or Register to Ask a Question