Sorry. I didn't mean for that to sound like a job request. I was more concerned with properly describing my needs that I was at formating my request for suggestions.
I have other scripts that calculate averages for these columns, and I will include that code here. I would like to use DBD::CSV on this, but lack the SQL experience to properly script this with out looping over and over in the file.
# Connect to the database, (the directory containing our csv file(
+s))
my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");
# Associate our csv file with the table name 'results'
$dbh->{'csv_tables'}->{'results'} = { 'file' => "$CWD/$name"};
my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0");
$sth->execute;
my @origcols = @{$sth->{NAME}};
my @cols; # = @origcols;
shift @origcols; # Eliminate First Column
foreach ( @origcols ) {
push ( @cols , $_ ) unless /Bandwidth.*|MSTCPLoop.*/ ; # Remove
+ unwanted columns
};
my $avgSQL =
'SELECT '
. join(', ', map { "avg($_) \n" } @cols )
. ' FROM results';
my @avgs = $dbh->selectrow_array($avgSQL);
my %avgsHash;
@avgsHash{ @cols } = @avgs; # uses a hash slice to populate %avgs
+Hash
for ( @cols ) {
(my $changed = $_) =~ s/_/","/;
printf STATS ("\"%s\",%f\n", $changed, $avgsHash{$_});
}
Honestly, I have only made a couple of attempts at this. I'm really stumped on how to format the SQL query. I think that is someone could point me in the direction the SQL query should look like, I'd be able to create it.
| [reply]
[d/l] |
SQL is the unnecessary complexity you don't want in such a simple task. Besides DBD::CSV implements a very limited subset of SQL.
What you should do is just to have a single pass over your file reading a line at the time, build your hash table of statistics along the way while splitting the records, and print out the stats at the end.
my %stats;
open MYFILE, "<data.txt" or die "Can not open file: $!";
chomp(my $heading = <MYFILE>);
my @col = split /,/, $heading;
while (my $line = <MYFILE>) {
# build a hash for the input record
chomp($line);
my @rec = split /,/, $line;
my %rec;
@rec{@col} = @rec;
# collect stats
$stats{$rec{rec_id}}{BAL} += $rec{bal};
$stats{$rec{rec_id}}{NUM} ++;
}
# print the stats here
for my $id (keys %rec) {
if ($rec{$id}{NUM}) {
print "$id => ", $rec{$id}{BAL}/$rec{$id}{NUM}, "\n"
}
}
close MYFILE;
| [reply]
[d/l] |
I'm getting the following error, but everything looks initialized to me..
Use of uninitialized value in hash element at ./top.pl line 19, <MYFIL
+E> line 994.
Use of uninitialized value in addition (+) at ./top.pl line 19, <MYFIL
+E> line 994.
Use of uninitialized value in hash element at ./top.pl line 20, <MYFIL
+E> line 994.
| [reply]
[d/l] |
