Re: Creating a csv from two database tables

[linuxer]

Well, you could write a script for that job?

I didn't get the idea of your input data? Is it stored in text files which are formatted in the way you've shown above? Or are they stored in a database?

Sorry for the previous question. I ignored the thread's subject. So, it's a database...

All in all, I see what you want to achieve, but not where your problem is. Can you please get more specific?

Do you need help with certain tasks? Do you want a written script?

Regards, Linuxer

Update:

- You might want to check DBI and the DBD::* modules for database access.

- I'm not used to create CSV files, but I think, there should also be CPAN modules for that: CSV ...

[joec_]

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";
}

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

[linuxer]

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__

[linuxer]

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?

[joec_]

yeah thats right - the csv headers come from the first table