Perl script to export sql query to csv

Question

The code below works, but all of the data displays in one row(but different columns) when opened in Excel. The query SHOULD display the data headings, row 1, and row 2. Also, when I open the file, I get a warning that says "The file you are trying to open,'xxxx.csv', is in a different format than specified by the file extension. Verify that the file is not corrupted...etc. Do you want to open the file now?" Anyway to fix that? That may be the cause too.

tldr; export to csv with multiple rows - not just one. fix Excel error. Thanks!

#!/usr/bin/perl
use warnings;
use DBI;
use Text::CSV;


# local time variables
($sec,$min,$hr,$mday,$mon,$year) = localtime(time);
$mon++;
$year += 1900;

# set name of database to connect to
$database=MDLSDB1;

# connection to the database
my $dbh = DBI->connect("dbi:Oracle:$database", "", "")
or die "Can't make database connect: $DBI::errstr\n";

# some settings that you usually want for oracle 10 
$dbh->{LongReadLen} = 65535; 
$dbh->{PrintError} = 0;  

# sql statement to run
$sql="select * from eg.well where rownum < 3";

my $sth = $dbh->prepare($sql);
$sth->execute();


my $csv = Text::CSV->new ( { binary => 1 } )             
or die "Cannot use CSV: ".Text::CSV->error_diag (); 

open my $fh, ">:raw", "results-$year-$mon-$mday-$hr.$min.$sec.csv"; 

$csv->print($fh, $sth->{NAME});

while(my $row = $sth->fetchrow_arrayref){      

$csv->print($fh, $row);
}

close $fh or die "Failed to write CSV: $!"; 

Solution 1

while(my $row = $sth->fetchrow_arrayref){   
  $csv->print($fh, $row);
  $csv->print($fh, "\n");
}

CSV rows are delimited by newlines. Just simply add a newline after each row.

Solution 2

I think another solution is to use the instantiation of the Text::CSV object and pass along the desired line termination there...

my $csv = Text::CSV->new ( { binary => 1 } )             
  or die "Cannot use CSV: " . Text::CSV->error_diag();

becomes:

my $csv = Text::CSV->new({ binary => 1, eol => "\r\n" })
  or die "Cannot use CSV: " . Text::CSV->error_diag();