Perl script help

[anilsingh]

I have multiple snapshotfile named "dbsnap.out","dbsnap2.out","dbsnap3.out"...etc.
I have written a PERL script(directory.pl) to extract some important data from all those snapshot files.
My next aim is to and create an output excel sheet(oe CSV format)
(as attached named "output.xls").
Could you please help me in build a PERL script to get such output?

[Matti_Kurkela]

You'll want the Spreadsheet::WriteExcel perl module. It depends on a few other modules, so you'll probably want to use the CPAN Shell to install it.

Example procedure for installation:

# perl -MCPAN -e shell

(If this is your first time with the CPAN shell, it asks you some questions about CPAN shell configuration here. With modern versions of Perl and CPAN shell module, the first question is "Would you like me to configure as much as possible automatically?"
If you answer "yes" and your system can access the CPAN servers through the Internet, you probably don't need to answer anything else.)

cpan[1]> install Spreadsheet::WriteExcel

(This command downloads, installs and compiles Spreadsheet::WriteExcel and all its dependencies automatically.)

See the documentation at CPAN:
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.25/lib/Spreadsheet/WriteExcel.pm

For a simple example, see the Quick Start subsection.

MK

[Hein van den Heuvel · Accepted solution]

anilsingh,

Welcome to the (wrong) forum.

It is refreshing to see a reasonably well documented topic, and a solid initial attempt to solve your problem yourself. Excellent.

The question does not appear to have anything to do with the forum though! There is NO hpux component. I see Windows style directories and AIX provided data files :-(.

anyway...

Your solution goes over the data from each line in the many times for no good reason. Once is enough! Also don't specify a directory string ( C:/perl/myfiles ) twice. Too error prone!

Solution outline:
- First get an lookup table (associative array) of interesting labels.
- Next process each file
- For each line read, see if it has interesting
data. Skip to next if no, process if yes.
Then at the end of each file print all values found, seperated by a comma for CSV output.

Below you'll find some (working!) Perl code to get you going on that route.

For full XLS support, follow Matti's advice.

Cheers,
Hein.

#!/usr/bin/perl
use strict;
my $DIR = '/cygdrive/c/temp';

my $parameter="Snapshot timestamp-High water mark for connections-Application connects-Secondary

connects total-Lock waits-Time database waited on locks-Deadlocks detected-Lock escalations-Lock

Timeouts-Total sorts-Total sort time (ms)-Sort overflows-Active sorts-Rows deleted-Rows

inserted-Rows updated-Rows selected-Rows read";

my @gy=split(/-/, $parameter); #splitting each parameter for purpose of checking in the file

my ( %gy, @values, $i );
my $filecount = 0;
#
# Create an associative array with all desired values, mapping to column numbers.

$gy{ substr( $gy[$_] . ' 'x30, 0, 30) } = $_ for (0..@gy-1);


my ($line, @alllines, $gy);

foreach my $file (<$DIR/dbsnap*.out>) {
print join (',',@gy),"\n" unless $filecount++;
$values[$_] = '' for (0..@gy-1); # clear values line for each file
open(FH,"<$file") or die "Could not open input file $file\n$!";
while () {
$i = $gy{ substr( $_, 0, 30) }; # Interesting line?
next unless defined($i);
m/=\s(.*)$/;
$values[$i] = $1;
}
print join (',',@values),"\n";
close(FH);
}
print STDERR "$filecount files processed.";

[anilsingh]

Thanks a lot for the help.It was a good learning for me from the script.One code snap I couldn't understand:

$gy{ substr( $gy[$_] . ' 'x30, 0, 30) } = $_ for (0..@gy-1);

What is the significance of column 30??

Also the final output is a bit different than the desired one.As I am planning to draw graph from the final CSV output(X-axis: Timestamp; Y-axis: all the values for that timestamp).So could someone help me in that formatting part(if possible without installing EXCEL modules for PERL).Please find the present output CSV and the desired one.

[anilsingh]

In beetween I just wrote a macro in Excel for transpose of column-row and it works properly.But it will be a good if I can remove the manual macro running part by using PERL itself :-)

[Hein van den Heuvel]

>> $gy{ substr( $gy[$_] . ' 'x30, 0, 30) } = $_ for (0..@gy-1);

> What is the significance of column 30??

It is a column where all labels appear to be unique for a while.
The code just a brute force way to pad everything out with 30 spaces before truncating to 30. The first 30 is 'overkill'. It should be at least 30 minute the shortest label.

It is possibly clearer / cleaner to write :
$gy{ sprintf("%-31s",$gy[$_]) } = $_ for (0..@gy-1);
But that does not truncate, so you would need to know the length of the longest label throughout (31, not 30)

For the time stamp you need to special case that column.

For example.
After...
$values[$i] = $1;
Add..

next if $i; # exception for columns 0, the timestamp
$values[$i] =~ s/.*\s([0-9:]+)\..*/\1/; # retain only hh:mm:ss


Or add..
next if $i; # exception for columns 0, the timestamp
$values[$i] = substr($1,11,8); # retain only hh:mm:ss