in reply to Re^8: Database processing
in thread DBI::st=HASH output

How'd you handle the NULL's? I was about to say that options include converting the NULL's to 0's in your pre-processing step (maybe something like s/,(?=,)/,0/g, but doesn't work for last col) .. or you could run a bunch of "UPDATE results SET colname = 0 WHERE colname = ''" statements .. both of those, of course assume you want them treated as 0's and not as NULL's (will greatly affect the AVG value).

As for output, perhaps something like this (depends on what you need in terms of cols) (note change in $dbh method used): 
my @avgs  = $dbh->selectrow_array($avgSQL);
print join(",", @cols) . "\n";
print join(",", @avgs) . "\n";

[download]

Replies are listed 'Best First'.
Re^10: Database processing
by DrAxeman (Scribe) on Aug 06, 2005 at 15:28 UTC
    Excellent! That's awesome. Thank you! I think I even understand what we did! :-)

    The approach I was trying to take was an example I found in the Data::Dumper page. I added: 
        $Data::Dumper::Pair = ",";
    print Dumper($avgsHashRef);

    [download]
    I was able to get output in 2 columns, which was good, but it was wrapped with the $VAR1 = {, and the data was indented. I was looking for a way to remove that and just get the data. If I later decide to go with a 2 column output, is there a way to strip that output down?

    Thank you again VERY MUCH!
[reply]
[d/l]
      Sure -- you have the colnames in @cols and the values in @avgs, so can just loop over them. 
      my @avgs  = $dbh->selectrow_array($avgSQL);

# CSV output method A
print join(",", @cols) . "\n";
print join(",", @avgs) . "\n";

# CSV output method B (2-columns)
print "Col,Avg\n";
printf("%s,%f\n", $cols[$_], $avgs[$_]) for 0 .. $#cols;

      [download]
      Note you can adjust the printf format according if you want a fixed number of decimal places or whatever.


      And one more way to do the same thing, which i introduce because it could be handy if you're going to process these avgs at all later in your code. And that is to hash them up for easy access: 
      my %avgsHash;
@avgsHash{ @cols } = @avgs;  # uses a hash slice to populate %avgsHash
print "Col,Avg\n";
printf("%s,%f\n", $_, $avgsHash{$_}) for @cols;

      [download]
[reply]
[d/l]
[select]
        This is great! One more question.

        Part of my "pre-processing" requires me to open the csv files in OpenOffice. I'd like to eliminate this piece. The major piece of this is making sure the data is converted into decimal. There is a module Data::Types that will allow me to conver to decimal. See http://search.cpan.org/~dwheeler/Data-Types-0.05/lib/Data/Types.pm

        I'm not sure how to write the loop that will convert all the data into decimal.

        I'd also like to be able to delete some columns that are not needed, like the first one, and any that's header contains MSTCPLoopback, or columns whose header ends with Bandwidth.

        I am trying to use the ALTER statement but it's not found. Or would it be better to just skip the columns while we are processing them?
[reply]

In Section Seekers of Perl Wisdom