How to convert date values ​​from Oracle to Excel using Perl DBI?

I am having problems with a very simple Perl process. I am basically querying an Oracle database and I want to load it into Excel. I was able to use DBIx :: Dump and it works. However, I need to be able to use the various Excel formatting tools. And I think Spreadsheet :: WriteExcel is the best module that outputs to Excel, which allows me to do more formatting.

Below is the code and the error I am getting. I am basically querying Oracle, fetching data, loading it into an array and trying to write to Excel. For some reason it is doing some kind of comparison and doesn't like data types. For example, the date is "25 -OCT-08". SVP - "S01". They seem to be non-numeric.

Mistake:

Argument "01-NOV-08" isn't numeric in numeric ge <>=> at C:/Perl/site/lib/Spreadsheet/WriteExcel/Worksheet.pm line 3414.
Argument "01-NOV-08" isn't numeric in pack ge <>=> ge <>=> at C:/Perl/site/lib/Spreadsheet/WriteExcel/Worksheet.pm line 2157.

      

code:

#!/usr/bin/perl -w 

#Set the Perl Modules
use strict; 
use DBI;
use Spreadsheet::WriteExcel;


# Connect to the oracle database
my $dbh = DBI->connect( 'dbi:Oracle:xxxx',
                        'xxxx',
                        'xxxx',
                      ) || die "Database connection not made: $DBI::errstr";


#Set up Query
my $stmt = "select 
                                   week_end_date, SVP, RD,
                                    DM, store, wtd_smrr_gain,QTD_SMRR_GAIN,
                                   wtd_bor_gain,QTD_BOR_GAIN,
                                   wtd_cust_gain,QTD_CUST_GAIN,
                                   wtd_CARD_CLOSED_OCT25,QTD_AVG_CARD_CL
                    from 
                          bonus_4Q_store
                    order by
                          store"; 

#Prepare Query
my $sth = $dbh->prepare($stmt); 

#Execute Query
$sth->execute() or die $dbh->errstr; 

my( $week_end_date,$SVP,$RD,$DM,$store,
    $wtd_smrr_gain,$QTD_SMRR_GAIN,
    $wtd_bor_gain,$QTD_BOR_GAIN,
    $wtd_cust_gain,$QTD_CUST_GAIN,
    $wtd_CARD_CLOSED_OCT25,$QTD_AVG_CARD_CL);

#binds each column to a scalar reference
$sth->bind_columns(undef,\$week_end_date,\$SVP,\$RD,\$DM,\$store,
                   \$wtd_smrr_gain,\$QTD_SMRR_GAIN,
                   \$wtd_bor_gain,\$QTD_BOR_GAIN,
                   \$wtd_cust_gain,\$QTD_CUST_GAIN,
                   \$wtd_CARD_CLOSED_OCT25,\$QTD_AVG_CARD_CL,);

#create a new instance
my $Excelfile = "/Test_Report.xls"; 
my $excel = Spreadsheet::WriteExcel->new("$Excelfile"); 
my $worksheet = $excel->addworksheet("WOW_SHEET");

#Create array shell
my @data;

#Call data and Write to Excel  
while ( @data = $sth->fetchrow_array()){ 
    my $week_end_date = $data[0]; 
    my $SVP = $data[1]; 
    my $RD = $data[2]; 
    my $DM = $data[3]; 
    my $store = $data[1]; 
    my $wtd_smrr_gain = $data[2]; 
    my $QTD_SMRR_GAIN = $data[3];
    my $wtd_bor_gain = $data[4];
    my $QTD_BOR_GAIN = $data[5];
    my $wtd_cust_gain = $data[6];
    my $QTD_CUST_GAIN = $data[7];
    my $wtd_CARD_CLOSED_OCT25 = $data[8];
    my $QTD_AVG_CARD_CL = $data[9];
    my $row = 0; 
    my $col = 0; 
    foreach my $stmt (@data) 
    { 
        $worksheet->write($row++, @data); 
        last; 
    } 
} 

print "DONE \n"; 
$sth->finish(); 
$dbh->disconnect();

      

+1


source to share


2 answers


The problem is here:

foreach my $stmt (@data) 
{ 
    $worksheet->write($row++, @data); # !!
    last; 
} 

      

Correct syntax for write()

:

write($row, $column, $token, $format)

      



You're missing an argument $column

, which in this case is probably 0.

If $stmt

is a ref array, you can write it in one go like this:

$worksheet->write($row++, 0, $stmt); 

      

+4


source


I would assume it comes out as a string, and when you try to insert it into a date column, there is no implicit conversion for it.

Try to pick a date like this and it will turn it into a char that you can use for comparison.

to_char(date, 'YYYY/MM/DD HH24:MI:SS')

      

then



to_date(date, 'YYYY/MM/DD HH24:MI:SS') 

      

to convert it back to the insert date. This is usually what you need to do in SQL.

As far as I remember, perl has a trace facility for the DBI that could better understand what's going on.

+3


source







All Articles