Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Re: Creating a csv from two database tables

by linuxer (Curate)
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


- 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

Replies are listed 'Best First'.
Re^2: Creating a csv from two database tables
by joec_ (Scribe) on Apr 06, 2009 at 15:12 UTC

    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 :))



    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__

      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!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://755752]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2017-10-21 07:07 GMT
Find Nodes?
    Voting Booth?
    My fridge is mostly full of:

    Results (269 votes). Check out past polls.