shilpam has asked for the wisdom of the Perl Monks concerning the following question:

This node falls below the community's minimum standard of quality and will not be displayed.
  • Comment on save the result of sql query in csv file

Replies are listed 'Best First'.
Re: save the result of sql query in csv file
by astroboy (Chaplain) on Mar 24, 2004 at 11:22 UTC
    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
[reply]
[d/l]

      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.
Re: save the result of sql query in csv file
by jZed (Prior) on Mar 24, 2004 at 15:30 UTC
    Use DBD::CSV. That will solve all of the problems mentioned above about embedded commas, embedded newlines, adding column names, etc.
[reply]
Re: save the result of sql query in csv file
by Anonymous Monk on Mar 24, 2004 at 11:00 UTC
    That's great, but which part are you having trouble with?
[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: save the result of sql query in csv file
by periapt (Hermit) on Mar 24, 2004 at 14:39 UTC
    You could save column headings a couple of ways. The easiest is to simply define the column headings in an array and then print them out with a join. If the headings vary, you can usually pull the column headings from the db connection. The following example works for ODBC but I believe that DBI has a similar option. I cobbled this snippet from programs that I have in production so I've skipped the setup, initialization and most of the error checking that should accompany the code.

        my ($rtncd, $db) = OpenODBC($cfg{obtsdsn}, $cfg{dbf01});
    my $colnames = '';
    my $outfile = 'testfile.csv';
    my $sqlstmt = '';
    my @rowdata = ();

    open(OUTFILE, ">$outfile") || 
         die "Could not open output file $outfile\n$!\n";

    $colnames = join(',',@{GetColNames($db,$tblname)});
    print OUTFILE $colnames."\n";

    $sqlstmt = qq|SELECT TOP 10 * FROM $tblname|;
    if($db->Sql("$sqlstmt")){
        $rtncd = -1;
        print "error!!Could not query table ".
              "$tblname\n$sqlstmt\n".$db->Error();
    }else{
        while($db->FetchRow()){
                              # retrieve and initialize data
            @rowdata = map{ $_ ||= '' } $db->Data();
                         # doesn't account for comma in data
            print OUTFILE join(',',@rowdata);
            print OUTFILE "\n"; # one per line
        }
    }
    close(OUTFILE);
    $db->Close;
    
    exit;
} #end main()
############################################################;
sub GetColNames{
    my $db = shift;
    my $tblname = shift;
    my $rtncd = 0;
    my $sqlstmt = qq|SELECT TOP 1 * FROM $tblname|;
    my @colnames = ();
    
    if($db->Sql("$sqlstmt")){
        $rtncd = -1;
        print "error!![0105]Could not query table ".
              "$tblname\n$sqlstmt\n".$db->Error();
    }else{
        if($db->FetchRow()){
            push @colnames, $db->{fnames}[$_] 
                                  for (0..$#{$db->{fnames}});
        }
    }
    
    return \@colnames;
} #end GetColNames()
############################################################;

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom