Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^8: Database processing

by DrAxeman (Beadle)
on Aug 06, 2005 at 14:35 UTC ( #481488=note: print w/ replies, xml ) Need Help??


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

I'm so close I can taste it!

The Data Restrictions section had the answer. The NULL values were what was killing me. I'm gonna look into DBD:AnyData.

I hate to keep asking questions, but I'm a Perl Novice. These files that I am working with could contain any number of NULL values. What would be the best way of handling these?

--SCRATCH THAT--

I think I got it. Now I just have 1 last question. How do I get it to output in csv format?


Comment on Re^8: Database processing
Re^9: Database processing
by davidrw (Prior) on Aug 06, 2005 at 15:13 UTC
    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";
      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);
      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!
        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;
        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;

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://481488]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (12)
As of 2014-08-20 08:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (107 votes), past polls