Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

adding database values to csv file

by vsurend (Novice)
on Sep 26, 2011 at 07:01 UTC ( #927802=perlquestion: print w/replies, xml ) Need Help??
vsurend has asked for the wisdom of the Perl Monks concerning the following question:

Hello PerlMonks, I have a table in my database which has more than 10 columns and also many data. I want those data from the database table to be saved to a cvs file while clicking a button. Can you please help me with a sample code. Thanks, Vidya Surendran

Replies are listed 'Best First'.
Re: adding database values to csv file
by Corion (Pope) on Sep 26, 2011 at 07:27 UTC
Re: adding database values to csv file
by dHarry (Abbot) on Sep 26, 2011 at 07:40 UTC

    Most databases offer functionality to allow you to dump the contents in various formats. Maybe you can make use of those tools? They typically give good performance.

    Depending on the database brand it might be possible to use a SQL statement to produce a csv formatted output file, e.g. something like (mySQL):

    SELECT col1, col2, col3, ... FROM some_table INTO OUTFILE '/tmp/some_table.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'



Re: adding database values to csv file
by Anonymous Monk on Sep 26, 2011 at 07:25 UTC
    Sure, see DBI and Text::CSV, the SYNOPSIS section of documentation always gives sample code
Re: adding database values to csv file
by Tux (Abbot) on Sep 26, 2011 at 08:53 UTC

    Buttons are GUI specific, so unless you tell us what the GUI is you want it to be implemented in, it will be hard to get working examples. perl/Tk button code won't work in Wx and vise-versa.

    A callback to dump a table to CSV could be something like (untested!)

    # assuming the database handle in $dbh use Text::CSV_XS; sub save_table { my $tbl = shift; # it is up to you to check for SQL-injection on $tbl ... my $sth = $dbh->prepare ("select * from $tbl"); $sth->execute; my %rec; my @fld = @{$sth->{NAME_lc}}; $sth->bind_columns (\@rec{@fld}); my $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => + "\n", always_quote => 1 }); $csv->print (*STDOUT, \@fld); # Print header, change STDOUT to fil +e if required while ($sth->fetch) { $csv->print (*STDOUT, [ @rec{@fld} ]); } } # save_table

    Enjoy, Have FUN! H.Merijn
      I am developing a GUI in perl/tk. Its mainly for report preparation purpose. So When I give the required inputs and Click on 'Generate Report' button I want the query fetched result to be put in a csv file. -Vidya Surendran
        $frame->Button ( -text => "Save table FOO", -borderwidth => 1, -highlightthickness => 0, -command => sub { save_table ("foo"); }, )->pack;

        Enjoy, Have FUN! H.Merijn
      I would like to tie on with my question:
      say you need to modify the values bevor printing where could be the best point to do this?
        while ($sth->fetch) { $rec{fobar} =~ s{old}{new}; # change in a single co +lumn s/(?:\b|[0-9])USD\b/EUR/g for values %rec; # change all columns $csv->print (*STDOUT, [ @rec{@fld} ]); }

        Enjoy, Have FUN! H.Merijn

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://927802]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2018-07-23 08:24 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (459 votes). Check out past polls.