Thanks for the feedback ... I've made some cosmetic changes, learned a
bit more Perl and I think this looks more Perl-like ... I'm done with
playing here for now.
#!/usr/bin/perl
use strict;
use warnings;
# use Spreadsheet::Read;
# my $ref = ReadData ("utility_daily_avgs.csv", sep => ";");
# print "maxrow=$ref->[0]{A1}\n";
use Text::CSV_XS;
use Date::Parse;
use Date::Format;
use PCP::LogImport;
my @handle = (); # pmi* handles, one per data column
my $debug = 0;
my $nrow = 0; # spreadsheet rows processed
my $stamp; # tv_sec to use in pmiWrite()
my $timedate; # date munged so Date::Parse is not confused
# args to be handled more elegantly ... 8^)>
#
my $archive = "petr";
my $mysheet = "utility_daily_avgs.csv";
sub dodate($)
{
# convert datetime format DD/MM/YYYY HH:MM:SS ... from OpenOffice
# Spreadsheet CSV into the format DD-Mmm-YYYY HH:MM:SS that
# Date::Parse seems to be able to parse correctly
#
my ($datetime) = @_;
my @fields = split(/\//, $datetime);
my $mm;
my %mm_map = (
"01" => "Jan", "02" => "Feb", "03" => "Mar", "04" => "Apr",
"05" => "May", "06" => "Jun", "07" => "Jul", "08" => "Aug",
"09" => "Sep", "10" => "Oct", "11" => "Nov", "12" => "Dec",
);
$#fields == 2 or die "dodate: bad datetime format: \"$datetime\"\n";
$mm = $fields[1];
if ($mm < 10 && $mm !~ /^0/) { $mm = "0" . $mm }; # add leading zero
defined($mm_map{$mm}) or die "dodate: bad month in datetime:
\"$datetime\"\n";
return $fields[0] . "-" . $mm_map{$mm} . "-" . $fields[2];
}
# data set-specific initialization ... arg is number of columns in
# first row of spreadsheet ... this should be timestamp followed
# by the number of metric-instances we're expecting
#
# first row of Petr's modified spreadsheet
#
"DATE";"T_OP_L";"T_OP_P";"T_MP1_L";"T_MP1_P";"T_MP2_L";"T_MP2_P";"T_NV";"CTR";"DELTA"
#
sub init($)
{
my ($ncol) = @_;
my $sts;
pmiStart($archive, 0);
pmiAddMetric("t_op", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1),
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"t_op\", ...): " . pmiErrStr(-1) . "\n";
pmiAddMetric("t_mp1", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1),
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"t_mp1\", ...): " . pmiErrStr(-1) . "\n";
pmiAddMetric("t_mp2", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1),
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"t_mp2\", ...): " . pmiErrStr(-1) . "\n";
pmiAddMetric("t_nv", PM_ID_NULL, PM_TYPE_FLOAT, PM_INDOM_NULL,
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"t_nv\", ...): " . pmiErrStr(-1) . "\n";
pmiAddMetric("fake.ctr", PM_ID_NULL, PM_TYPE_32, PM_INDOM_NULL,
PM_SEM_COUNTER, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"fake.ctr\", ...): " . pmiErrStr(-1) . "\n";
pmiAddMetric("fake.delta", PM_ID_NULL, PM_TYPE_32, PM_INDOM_NULL,
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0)) == 0
or die "pmiAddMetric(\"fake.delta\", ...): " . pmiErrStr(-1) . "\n";
pmiAddInstance(pmInDom_build(245,1), "low", 1) == 0
or die "pmiAddInstance(..., \"low\"): " . pmiErrStr(-1) . "\n";
pmiAddInstance(pmInDom_build(245,1), "peak", 2) == 0
or die "pmiAddInstance(..., \"peak\"): " . pmiErrStr(-1) . "\n";
push(@handle, -1); # col 1 is timestamp, not data
$sts = pmiGetHandle("t_op", "low");
$sts >= 0 or die "pmiGetHandle(\"t_op\", \"low\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_op", "peak");
$sts >= 0 or die "pmiGetHandle(\"t_op\", \"peak\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_mp1", "low");
$sts >= 0 or die "pmiGetHandle(\"t_mp1\", \"low\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_mp1", "peak");
$sts >= 0 or die "pmiGetHandle(\"t_mp1\", \"peak\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_mp2", "low");
$sts >= 0 or die "pmiGetHandle(\"t_mp2\", \"low\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_mp2", "peak");
$sts >= 0 or die "pmiGetHandle(\"t_mp2\", \"peak\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("t_nv", "");
$sts >= 0 or die "pmiGetHandle(\"t_nv\", \"\"): " . pmiErrStr($sts) . "\n";
push(@handle, $sts);
$sts = pmiGetHandle("fake.ctr", "");
$sts >= 0 or die "pmiGetHandle(\"fake.ctr\", \"\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
$sts = pmiGetHandle("fake.delta", "");
$sts >= 0 or die "pmiGetHandle(\"fake.delta\", \"\"): " . pmiErrStr($sts) .
"\n";
push(@handle, $sts);
if ($debug) {
for (my $v = 0; $v < @handle; $v++) {
print "handle[$v] = $handle[$v]\n";
}
}
$#handle == $ncol-1 or
die "init: $#handle metric-instance definitions does not equal " .
($ncol-1) . " data columns\n";
}
my $csv = Text::CSV_XS->new ({ sep_char => ";", binary => 1 }) or
die "Cannot use CSV: ".Text::CSV->error_diag();
open my $fh, "<:encoding(utf8)", $mysheet or die "$mysheet: $!";
# process spreadsheet one row at a time ...
#
while (my $row = $csv->getline ($fh)) {
$nrow++;
if ($nrow == 1) {
# set up PCP::LogImport and skip first row of column headings
init(scalar(@$row));
next;
}
$timedate = dodate($row->[0]);
$stamp = str2time($timedate);
$debug and print "in: $row->[0] reformat: $timedate stamp: $stamp ctime: "
. ctime($stamp) . "\n";
for (my $v = 1; $v < @handle; $v++) {
pmiPutValueHandle($handle[$v], $row->[$v]) == 0 or
die "pmiPutValueHandle: row: $nrow col: $v : " . pmiErrStr(-1) .
"\n";
}
pmiWrite($stamp, 0) == 0 or die "pmiWrite: row: $nrow : " . pmiErrStr(-1) .
"\n";
}
$csv->eof or $csv->error_diag();
close $fh;
pmiEnd() == 0 or die "pmiEnd: " . pmiErrStr(-1) . "\n";
print "Imported $nrow data rows, archive $archive created.\n";
|