Formatting a file.

[malpa]

Hi

I have a file with many records

File.txt (5000000 records per day)

03102159808 0012012459595 060604132938date1339350009900099swichP00002168100service1011route_inroute_outO00000N000 0000000000957
..
...
..

You can find many switches with many routes and many services for each one of them.

I want to do this


date1 date2 date 3 ...


switch;route;service minutes minutes minutes ...
switch1;route1;service1 minutes minutes minutes ...
switch1;route1;service2 minutes minutes minutes ...
....
....

I use dynamic tables (EXCEL o ACCESS) to obtain a resume, I wanto to do this with awk.



Thanks for your assistance.

 

[PHV]

Thanks for your assistance
And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[malpa]

Hi

I did a program in awk to do this, but this program spend 45 minutes to do this (5 000 000 of records). I tried but i need assistance to do this.

BEGIN{

d[1]= "060601"
d[2]= "060602"
d[3]= "060603"
d[4]= "060604"
d[5]= "060605"
d[6]= "060606"
d[7]= "060607"
d[8]= "060608"
d[9]= "060609"
d[10]="060610"
d[11]="060611"
d[12]="060612"
d[13]="060613"
d[14]="060614"
d[15]="060615"
d[16]="060616"
d[17]="060617"
d[18]="060618"
d[19]="060619"
d[20]="060620"
d[21]="060621"
d[22]="060622"
d[23]="060623"
d[24]="060624"
d[25]="060625"
d[26]="060626"
d[27]="060627"
d[28]="060628"
d[29]="060629"
d[30]="060630"
d[31]="060631"



}
{
marc[substr($0,55,6)";"substr($0,89,10)";"substr($0,120,7)";"substr($0,111,6)]++
switch_route_service[substr($0,89,10)";"substr($0,120,7)";"substr($0,111,6)]++
duration[substr($0,55,6)";"substr($0,89,10)";"substr($0,120,7)";"substr($0,111,6)] += ( substr($0,170,2)*3600 + substr($0,172,2)*60 + substr($0,174,2) ) / 60


}
END{

printf"\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\n",d[1],d[2],d[3],d[4],d[5],d[6],d[7],d[8],d[9],d[10],d[11],d[12],d[13],d[14],d[15],d[16],d[17],d[18],d[19],d[20],d[21],d[22],d[23],d[24],d[25],d[26],d[27],d[28],d[29],d[30],d[31] > FILENAME"_incoming.txt"

for ( c in swich_route_service ){

printf"%s\t",c > FILENAME"_incoming.txt"
for ( f=1; f<=31; f++){
matcha=0
for ( m in marc){
if ( m ~ c && m ~ d[f] ){
matcha=1
printf "\t%d\t%.3f",marc[m],duration[m] > FILENAME"_incoming.txt"
}
}
if( !matcha){cero=0;printf "\t%d\t%.3f",cero,cero > FILENAME"_incoming.txt" }
}
print "\n" > FILENAME"_incoming.txt"
}
}




The input format is like this
many registers or records of many switches.

I made a cat *.* of many switches files.

file_in.txt

subsriber_a subcriber_b date switch incoming_route service HHMMSS (call duration)
subsriber_a subcriber_b date switch incoming_route service HHMMSS (call duration)
subsriber_a subcriber_b date switch incoming_route service HHMMSS (call duration)

This is the ouput format, I want a resume per switch per route per service per dia with the calls and minutes.

file_out.txt
switch incomming route service 60601 60602
total calls total minutes total calls total minutes
SWITCH1 ROUTEn service_a 9 1 0 0
SWITCH2 ROUTEm service_b 73 269 0 0
SWITCH3 ROUTEx service_c 0 0 0 0
SWITCH4 ROUTEy service_a 498 950 0 0
SWITCH5 ROUTEz service_b 85 0 0 0



Again thanks a lot for your timely assistance.

malpa

 

[PHV]

You may try the following changes:
BEGIN{
d[1]= "060601"
...
}
{
c=substr($0,89,10)";"substr($0,120,7)";"substr($0,111,6)
m=substr($0,55,6)";"c
marc[m]++
switch_route_service[c]++
duration[m] += ( substr($0,170,2)*60 + substr($0,172,2) + substr($0,174,2)/60 )
}
END{
...
for ( c in swich_route_service ){
printf"%s\t",c > FILENAME"_incoming.txt"
for ( f=1; f<=31; f++ ){
m=d[f]";"c
printf "\t%d\t%.3f",marc[m],duration[m] > FILENAME"_incoming.txt"
}
print "\n" > FILENAME"_incoming.txt"
}
}

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[malpa]

Hi,

Thanks for your timely assistance.

I made the changes and it works perfectly.

malpa.

 

[PHV]

but this program spend 45 minutes to do this
And now ?

 

[malpa]

Hi

PVH, you are right, but I don´t know how to reduce the time. I´m not in my office now, but when my computer is ready, I´ll try again.

Actually the only thing, that I could to say you is that with this program I don´t need to use Access or Excel.

In my weekend I'm going to work very hard on this.

malpa.