in reply to Re: Creating a csv from two database tables
in thread Creating a csv from two database tables

Hi,

An example script would be helpful - note my example listing is significantly reduced - the tables contain many thousands of rows in reality. I guess the way ive been going about it so far is:

while ($row = $sth->fetchrow_arrayref){
if (!defined($objects{$row->[0]})) ## not seen this object before, so 
+add it to the hash
{
        $objects{$row->[0]} = $row->[1];
}else{
        $objects{$row->[0]} = $objects{$row->[0]}.','.$row->[1]; ## se
+en this object so append to csv
}
}

while ($header_row=$header_sth->fetchrow_arrayref){
    $headers .= "\"$header_row->[1]\"".',';
}
print $headers,"\n";
while (($k,$v)=each(%objects)){
    print $v,"\n";
}

[download]

That is probably the worst way to go about it, and im not even sure it works all the time, or takes account for missing values. For example that will print out the headers, but the data items will not match if one value is missing

I hope this is clearer (probably not :))

Thanks

-----

Eschew obfuscation, espouse elucidation!

Replies are listed 'Best First'.
Re^3: Creating a csv from two database tables
by linuxer (Curate) on Apr 06, 2009 at 16:43 UTC

    For creating the objects hash, I would rely upon autovivification.

    And I would create a plain hash-of-hash of the data and create the csv format afterwards.

    here's my example, as I tried to figure your problem without database access (So I used DBD::CSV to have a "database"). It's just my hack, needs some modifications before production usage, and I'm sure there's a more efficient/stylish way to do that ;o) 

    #! /usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( 'DBI:CSV:f_dir=.' ) or die "cannot connect: " 
+. $DBI::errstr;

sub _get_param_fieldnames {

    my %field;

    my $sth = $dbh->prepare( 'SELECT param_id,name FROM x_param' )
        or die "cannot prepare: " . $dbh->errstr;

    $sth->execute or die $dbh->errstr;

    while ( my $row = $sth->fetchrow_arrayref ) {
        $field{ $row->[0] } = $row->[1];
    }

    $sth->finish;

    return \%field;
}

sub _get_object_entries {
    my %object;

    my $sth = $dbh->prepare( 'SELECT * FROM x_object' )
        or die "cannot prepare: " . $dbh->errstr;

    $sth->execute or die $dbh->errstr;

    while ( my $row = $sth->fetchrow_arrayref ) {
        $object{ $row->[0] }->{ $row->[1] } = $row->[2];
    }

    $sth->finish;

    return \%object;
}


# output stuff
{
    my $field_by  = _get_param_fieldnames();
    my $object_by = _get_object_entries();

    local $, = ',';
    local $\ = "\n";

    # create csv output; check for proper modules

    print 'OBJECT_ID', map { $field_by->{$_} }  sort { $a <=> $b } key
+s %$field_by;

    for my $oid ( keys %$object_by ) {

        my @param_values;

        for my $pid ( sort { $a <=> $b } keys %$field_by ) {
            push @param_values, exists $object_by->{$oid}->{$pid} ? $o
+bject_by->{$oid}->{$pid} : '';
        }

        print $oid, @param_values;
    }
}

$dbh->disconnect;

__END__

    [download]
[reply]
[d/l]
Re^3: Creating a csv from two database tables
by linuxer (Curate) on Apr 06, 2009 at 15:23 UTC

    As I tried to figure out your output format, it looks like you don't need the first db table (param)?

    All output refers only to the second table (object).

    So I wonder, for what do need the first table regarding your output?

    Ah, never mind. I think I got it. You use the "param" names from the first table for the header fields of the output... right?

[reply]
      yeah thats right - the csv headers come from the first table

      -----

      Eschew obfuscation, espouse elucidation!

[reply]

In Section Seekers of Perl Wisdom