http://www.perlmonks.org?node_id=761609

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

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: 
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

[download]

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;

[download]

Hagen Finley

Boulder, CO

Replies are listed 'Best First'.
Re: SpreadSheet::ParseExcel Failure to Match Question
by McDarren (Abbot) on May 03, 2009 at 23:36 UTC
    Could it be that $sheet->{Cells}[$row][3]->{Val} contains "non-printable" characters that prevent an exact match?

    You could check this by inserting a print statement, or simply use =~ instead of eq, eg. 

    if ($sheet->{Cells}[$row][3]->{Val} =~ m/UPGRADE SOLUTION/) {$upgrade=
+1} else {$upgrade=0};

    [download]
    Hope this helps,
    Darren :)
[reply]
[d/l]
[select]
      Thanks for the advice. I liked your matching idea - which I thought was promising but unfortunately it didn't seem to work any better than the eq. I have a print out of the variables I use for validation and it shows me that my match isn't working: 
      DE: 1002179816
DEDATE: 2009-5-4
OP# 996115
SO# 63170867
0 LINE# 1
1 QTY: 2
2 MODEL: CX-4PDAE-FD
3 DESC: 4G DAE FIELD INSTALL
4 UNIT: EA
5 LIST PRICE: 11800
6 DISCOUNT: 0.000
7 SELL PRICE: 0000
8 REP FLOOR: 0000
9 DM FLOOR: 0000
10 COST: 0000
11 MARGIN: 0.0000
12 MAINTENANCE: 0000
13 COMMENT: Seattle CX3-20 Expansion (30x300 15K)
14 SERIAL# 0
15 BU: IT
16 UPGRADE? 0
17 DEAL# 0

      [download]

      #14 should be a serial number such as APM1233432445. #16 should be a 1. Looks like there is something a little more complicated a foot. Thanks!

      Hagen
[reply]
[d/l]
        In that case, I would insert: 
        print "==$sheet->{Cells}[$row][3]->{Val}==\n";

        [download]
        in the appropriate place, so that you can see what the contents of that cell actually are.

        Cheers,
        Darren :)

[reply]
[d/l]

Back to Seekers of Perl Wisdom