Formatting output into a *.csv file 3

[chippymike]

Hi,

I have recently enabled performance monitoring on our server which produces a file and e-mails us every day. I'm trying to write a script which will ignore the textual info but take the numerical data and sort it into a csv file, allowing me to import it into excel to produce graphs of data to impress the bosses!
I want it to look something like this;

01:00:01, 3.0, 40
02:00:00, 2.9, 31
03:00:00, 1.5, 3
04:00:00, 1.7, 5 ....etc....etc...

I have a feeling I'll have to use "sed" and possibly "awk" but my scripting knowledge is limited and I'm not sure how to apply these to get the desired result.
The e-mail is below;

Report on Daily Performance
|------------------------------------------------------------+---------------------------------------------------------------|
Run Queue for server SERVER1

SunOS SERVER1 5.8 Generic_108528-19 sun4u 07/29/03

00:00:01 runq-sz %runocc swpq-sz %swpocc
01:00:01 3.0 40
02:00:00 2.9 31
03:00:00 1.5 3
04:00:00 1.7 5
05:00:00 1.7 1
06:00:01 1.0 0
07:00:00 1.0 0
08:00:00 1.2 15
08:20:00 1.0 1
08:40:00 1.1 7
09:00:00 1.0 2
09:20:00 1.1 1
09:40:00 1.0 1
10:00:00 1.0 1
10:20:00 1.3 4
10:40:00 1.1 2
11:00:00 1.0 0
11:20:00 1.0 1
11:40:00 2.4 1
12:00:01 1.0 1
12:20:00 1.8 1
12:40:00 1.0 0
13:00:00 1.2 2
13:20:00 1.6 1
13:40:00 1.0 0
14:00:00 1.0 0
14:20:00 1.6 2
14:40:00 2.0 1
15:00:00 1.0 0
15:20:00 1.0 1
15:40:00 1.4 1
16:00:00 1.3 4
16:20:00 1.1 2
16:40:01 1.0 0
17:00:00 1.0 1
17:20:00 1.0 0
17:40:00 1.0 1
18:00:00 1.0 0
19:00:00 1.2 6
20:00:00 1.0 0
21:00:00 1.0 0
22:00:00 1.3 5
23:00:01 1.1 4

Average 2.3 5
|------------------------------------------------------------+---------------------------------------------------------------|
|------------------------------------------------------------+---------------------------------------------------------------|
CPU Utilisation for server SERVER1

SunOS SERVER1 5.8 Generic_108528-19 sun4u 07/29/03

00:00:01 %usr %sys %wio %idle
01:00:01 41 33 0 26
02:00:00 35 26 0 39
03:00:00 18 14 0 68
04:00:00 19 15 0 65
05:00:00 2 4 0 94
06:00:01 1 3 0 96
07:00:00 1 3 0 96
08:00:00 18 16 0 66
08:20:00 2 3 0 95
08:40:00 14 16 0 70
09:00:00 6 8 0 86
09:20:00 6 7 0 87
09:40:00 13 16 0 72
10:00:00 13 16 0 71
10:20:00 20 18 0 62
10:40:00 8 6 0 85
11:00:00 2 3 0 95
11:20:00 3 5 0 92
11:40:00 4 5 0 92
12:00:01 2 3 0 95
12:20:00 3 4 0 93
12:40:00 1 3 0 96
13:00:00 7 5 0 88
13:20:00 6 5 0 89
13:40:00 1 3 0 96
14:00:00 3 4 0 93
14:20:00 6 4 0 90
14:40:00 3 4 0 93
15:00:00 2 3 0 95
15:20:00 18 3 0 78
15:40:00 3 4 0 92
16:00:00 7 4 0 88
16:20:00 7 6 0 86
16:40:01 3 4 0 94
17:00:00 3 4 0 93
17:20:00 1 3 0 96
17:40:00 2 4 0 94
18:00:00 2 4 0 94
19:00:00 16 16 0 68
20:00:00 1 3 0 96
21:00:00 1 3 0 96
22:00:00 6 7 0 86
23:00:01 12 17 0 71

Average 10 9 0 81
|------------------------------------------------------------+---------------------------------------------------------------|
MEMORY Utilisation for server SERVER1

SunOS SERVER1 5.8 Generic_108528-19 sun4u 07/29/03

00:00:01 freemem freeswap
01:00:01 321984 13318653
02:00:00 331692 13318064
03:00:00 326078 13439875
04:00:00 324684 13438462
05:00:00 321420 13359384
06:00:01 321482 13361951
07:00:00 321392 13357832
08:00:00 331870 13320450
08:20:00 345073 13355386
08:40:00 358468 13337508
09:00:00 346535 13334466
09:20:00 340657 13354088
09:40:00 321093 13354162
10:00:00 321049 13353955
10:20:00 331117 13352887
10:40:00 338437 13345417
11:00:00 338800 13353397
11:20:00 338788 13353231
11:40:00 338839 13351856
12:00:01 339205 13363829
12:20:00 338884 13351727
12:40:00 338966 13353584
13:00:00 336985 13345509
13:20:00 335983 13348611
13:40:00 336153 13353785
14:00:00 324974 13354238
14:20:00 320968 13347567
14:40:00 321145 13351315
15:00:00 321245 13354306
15:20:00 321135 13353525
15:40:00 321067 13352468
16:00:00 320763 13345907
16:20:00 320770 13346359
16:40:01 321333 13364818
17:00:00 321048 13353741
17:20:00 321049 13353669
17:40:00 320957 13352484
18:00:00 320898 13351558
19:00:00 319290 13295560
20:00:00 323505 13357201
21:00:00 323518 13357271
22:00:00 322162 13346932
23:00:01 321173 13357051

Average 326863 13354123
|----------+------------------------+------------------------|---------------+---------------+---------------+---------------|

Thanks for your help in advance! Everything I've tried comes out as a jumbled mess!

Cheers!

 

[chippymike]

Hi,

As a first step to tackling this problem, is there anyway to cut a line from the file if it begins with either a "0" a "1" or a "2"?
If so I could be well on my way to solving the problem.

Cheers

 

[Ygor]

Try...

awk '/^..:..:../ {
for (i=1; i<NF; i++)
printf &quot;%s, &quot;, $i ;
print $NF;
}' file1 > file2

 

[Ygor]

Sorry, I've just read your second post, so maybe all you want is...

grep ^[0-9] file1

 

[Salem]

This short AWK program replaces all white space with a single comma - but only for those lines which begin with a digit.

awk '/^[0-9]/{gsub("[ \t]+",",");}
    {print}' file.txt > file.csv

Produces this output

SunOS SERVER1 5.8 Generic_108528-19 sun4u    07/29/03

00:00:01,runq-sz,%runocc,swpq-sz,%swpocc
01:00:01,3.0,40,
02:00:00,2.9,31,
03:00:00,1.5,3,

 

[chippymike]

Great!

That's exactly what I was looking for!

Thanks

 

[TimeTraveler]

You can also try:

sed -e '/^[0-9]/s/ * /\&quot;,\&quot;/g' -e 's/^/\&quot;/' -e 's/$/\&quot;/' infile > outfile

This will add the quotes where necessary, which I've found Excel processes a little more predictably. I convert output to .csv a lot since the higher-ups at my dayjob seem to like seeing things in Excel.

Sean aka TimeTraveler

Visit

http://www.museworksentertainment.com

 

[chippymike]

Thanks!

You all get a star!