in reply to save the result of sql query in csv file

How about 
use DBI;
use strict;

my $dbh = DBI->connect('dbi:Oracle:dev', 'myUser', 'myPass') ||
    die "Cannot connect to database\n", $DBI::Errstr;

my $sth = $dbh->prepare("select * from emp"); 
$sth->execute || die "failed to execute:\n ", $DBI::Errstr;

open (FH, ">c:/temp/out.csv") || die "Cannot open file\n";
while (my @row = $sth->fetchrow_array) {
    print FH join(', ', @row), "\n";
}
close FH;
$dbh->disconnect;

[download]
This is a trvial solution. It won't handle commas in your data which may need to be wrapped in quotes or escaped

Replies are listed 'Best First'.
Re: Re: save the result of sql query in csv file
by valdez (Monsignor) on Mar 24, 2004 at 15:39 UTC

    To avoid problems with commas and quoting, use Text::CSV_XS or Text::xSV written by tilly; here it is an example using the first module:

    use Text::CSV_XS;

$csv = Text::CSV_XS->new({ 'quote_char'  => '"',
                           'escape_char' => '"',
                           'sep_char'    => ',',
                           'binary'      => 0,
                           'eol'         => "\r\n"
                         });

while (my @row = $sth->fetchrow_array) {
  if ($csv->combine(@row)) {
    print $csv->string;
  } else {
    my $err = $csv->error_input;
    print "combine() failed on argument: ", $err, "\r\n";
  }
}

    [download]

    Ciao, Valerio

[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

In Section Seekers of Perl Wisdom