Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: Creating a csv from two database tables

by linuxer (Deacon)
on Apr 06, 2009 at 14:56 UTC ( #755752=note: print w/ replies, xml ) Need Help??


in reply to Creating a csv from two database tables

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 ...


Comment on Re: Creating a csv from two database tables
Re^2: Creating a csv from two database tables
by joec_ (Scribe) on Apr 06, 2009 at 15:12 UTC
    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

    -----

    Eschew obfuscation, espouse elucidation!

      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?

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

        -----

        Eschew obfuscation, espouse elucidation!

      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__

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2014-12-26 10:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (171 votes), past polls