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();
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);
source to share
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.
source to share