adding database values to csv file

[vsurend]

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

[Corion]

Also see Querylet and Querylet::CGI, which do just that (and little more).

[dHarry]

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'

Cheers

Harold

[Anonymous Monk]

Sure, see DBI and Text::CSV, the SYNOPSIS section of documentation always gives sample code

[Tux]

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

[vsurend]

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

[Tux]

$frame->Button (
    -text               => "Save table FOO",
    -borderwidth        => 1,
    -highlightthickness => 0,
    -command            => sub { save_table ("foo"); },
    )->pack;

[Anonymous Monk]

Hello,
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?
Thank!
VE

[Tux]

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} ]);
    }