I have been having great success using Spreadsheet::ParseExcel, but like most people I am never completely satisfied. I have a small section of code which is not behaving as I would expect and I was hoping the patient and wise perl monks would once again provide their guidance.
The code section in question is as follows:
The first if statement works every time but the second and third never work. That leads me to believe the first if statement is impacting the value of $row but I don't see how or why that would happen. I am including the entire script below to provide context.
I extend my heartfelt appreciation to the perl monks for their kind assistance.
#!/usr/bin/perl
#Perl script to extract site data from Synergy and insert into mysql s
+mdb DATABASE bom TABLE
#mysql> desc bom;
#+---------+--------------+------+-----+---------+-------+
#| Field | Type | Null | Key | Default | Extra |
#+---------+--------------+------+-----+---------+-------+
#| indate | date | YES | | NULL | |
#| dedate | date | YES | | NULL | |
#| de | int(11) | NO | MUL | NULL | |
#| op | int(11) | NO | MUL | NULL | |
#| so | int(11) | YES | MUL | NULL | |
#| serial | char(20) | YES | | NULL | |
#| line | int(11) | YES | | NULL | |
#| qty | int(11) | YES | | NULL | |
#| model | char(30) | YES | MUL | NULL | |
#| mdesc | char(50) | YES | | NULL | |
#| unit | char(5) | YES | | EA | |
#| listp | int(11) | YES | | NULL | |
#| discp | int(11) | YES | | NULL | |
#| deal | int(11) | YES | | NULL | |
#| repfl | int(11) | YES | | NULL | |
#| dmfl | int(11) | YES | | NULL | |
#| cost | int(11) | YES | | NULL | |
#| marg | int(11) | YES | | NULL | |
#| maint | int(11) | YES | | NULL | |
#| comment | varchar(125) | YES | | NULL | |
#| ucost | int(11) | YES | | NULL | |
#| dealn | char(12) | YES | | NULL | |
#| bu | char(20) | YES | | NULL | |
#| upgrade | char(1) | YES | | NULL | |
#+---------+--------------+------+-----+---------+-------+
use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::ParseExcel;
use Time::JulianDay;
# DBI configuration variables
my $platform = "mysql";
my $database = "smdb";
my $host = "localhost";
my $port = "3306";
my $tablename = "site";
my $user = "root";
my $pw = "password";
#Data source name
my $dsn = "dbi:mysql:smdb:localhost:3306";
# PERL DBI connection handle
my $dbh = DBI->connect($dsn, $user, $pw)or die "Unable to connect: $DB
+I::errstr\n";
#xls file to be parsed
my $file = "/Transfer/Q2MasterQuoteList/QAR100100002v1.xls";
#global variables
our $row;
our $de;
our @de;
our $de0;
our $version;
our $of;
our $den;
our $den2;
our $year;
our $month;
our $day;
our $so;
our $op;
our $serial;
our $line;
our $qty;
our $model;
our $mdesc;
our $unit;
our $listp;
our $discp;
our $deal;
our $repfl;
our $dmfl;
our $cost;
our $marg;
our $maint;
our $bu;
our $comment;
our $dealn;
our $upgrade;
our $dedate;
our $dedate0;
our $dedate1;
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file)or die "
+Unable to open $file\n"; #This syntax never returns "Unable to open $
+file" even when the file doesn't exist
#locate columns in the spreadsheet from which we want to extract data
my $sheet =$workbook->{Worksheet}[0];
#iterate through spreadsheet rows and extract bill of materials items
+column by column
$de0 = $sheet->{Cells}[4][10]->{Val};
@de = split / /, $de0;
($de, $version, $den, $of, $den2) = @de;
if ($sheet->{Cells}[8][10]->{Val} eq "") {$so=0} else {$so = $shee
+t->{Cells}[8][10]->{Val}};
if ($sheet->{Cells}[11][12]->{Val} eq "") {$op=0} else {$op = $she
+et->{Cells}[11][12]->{Val}};
$dedate = $sheet->{Cells}[6][10]->{Val};
foreach my $row ($sheet->{MinRow}+18..$sheet->{MaxRow}){
if ($sheet->{Cells}[$row][2]->{Val} eq "SYSTEM") {$comment = $shee
+t->{Cells}[$row-2][2]->{Val}}; #This syntax returns the right v
+alue
if ($sheet->{Cells}[$row][3]->{Val} eq "UPGRADE SOLUTION") {$upgra
+de=1} else {$upgrade=0}; # This syntax always returns 0
+ even when the match should return a 1
if ($sheet->{Cells}[$row][4]->{Val} eq "") {$serial=0} else {$seri
+al=$sheet->{Cells}[$row][4]->{Val}}; # This syntax always returns 0 e
+ven when the match should return the value of $row,4
if ( $sheet->{Cells}[$row][0]->{Val} =~ /(^\d+)/ ) {
$line = $sheet->{Cells}[$row][0]->{Val};
$qty = $sheet->{Cells}[$row][1]->{Val};
$model = $sheet->{Cells}[$row][2]->{Val};
$mdesc = $sheet->{Cells}[$row][3]->{Val};
$unit = $sheet->{Cells}[$row][4]->{Val};
$listp = $sheet->{Cells}[$row][5]->{Val};
$discp = $sheet->{Cells}[$row][6]->{Val};
$deal = $sheet->{Cells}[$row][7]->{Val};
if ($sheet->{Cells}[$row][8]->{Val} eq "") {$repfl=0} else {$repf
+l = $sheet->{Cells}[$row][8]->{Val}};
if ($sheet->{Cells}[$row][9]->{Val} eq "") {$dmfl=0} else {$dmfl
+= $sheet->{Cells}[$row][9]->{Val}};
if ($sheet->{Cells}[$row][10]->{Val} eq "") {$cost=0} else {$cost
+ = $sheet->{Cells}[$row][10]->{Val}};
if ($sheet->{Cells}[$row][11]->{Val} eq "") {$marg=0} else {$marg
+=$sheet->{Cells}[$row][11]->{Val}};
if ($sheet->{Cells}[$row][12]->{Val} eq "") {$maint=0} else {$mai
+nt=$sheet->{Cells}[$row][12]->{Val}};
if ($sheet->{Cells}[$row][16]->{Val} eq "") {$serial=0} else {$se
+rial=$sheet->{Cells}[$row][16]->{Val}};
if ($sheet->{Cells}[$row][17]->{Val} eq "") {$bu='IT'} else {$bu
+= $sheet->{Cells}[$row][17]->{Val}};
if ($sheet->{Cells}[$row][18]->{Val} eq "") {$dealn=0} else {$dea
+ln= $sheet->{Cells}[$row][18]->{Val}};
$dedate0 = $dedate + julian_day(1900, 1, 0);
($year, $month, $day) = inverse_julian_day($dedate0);
$dedate1 = join '-', $year,$month,$day;
$dbh->do("insert into bom
(indate,dedate,de,op,so,serial,line,qty,model,mdesc,unit,listp,discp,d
+eal,repfl,dmfl,cost,marg,maint,bu,comment,dealn,upgrade)
values
(CURDATE(),\'$dedate1\',$de,$op,$so,\'$serial\',$line,$qty,\'$model\',
+\'$mdesc\',\'$unit\',$listp,$discp,$deal,$repfl,$dmfl,$cost,$marg,$ma
+int,\'$bu\',\'$comment\',\'$dealn\',$upgrade)");
#print captured output
print "DE: $de\n";
print "DEDATE: $dedate1\n";
print "OP# $op\n";
print "SO# $so\n";
print "0 LINE# $line\n";
print "1 QTY: $qty\n";
print "2 MODEL: $model\n";
print "3 DESC: $mdesc\n";
print "4 UNIT: $unit\n";
print "5 LIST PRICE: $listp\n";
print "6 DISCOUNT: $discp\n";
print "7 SELL PRICE: $deal\n";
print "8 REP FLOOR: $repfl\n";
print "9 DM FLOOR: $dmfl\n";
print "10 COST: $cost\n";
print "11 MARGIN: $marg\n";
print "12 MAINTENANCE: $maint\n";
print "13 COMMENT: $comment\n";
print "14 SERIAL# $serial\n";
print "15 BU: $bu\n";
print "16 UPGRADE? $upgrade\n";
print "17 DEAL# $dealn\n";
print "+++++++++++++++++++++++++++++++++++\n";
}{next;}
}
exit;
