Beefy Boxes and Bandwidth Generously Provided by pair Networks DiBona
Just another Perl shrine
 
PerlMonks  

SpreadSheet::ParseExcel Failure to Match Question

by finhagen (Sexton)
on May 03, 2009 at 19:20 UTC ( #761609=perlquestion: print w/ replies, xml ) Need Help??
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

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;

Hagen Finley

Boulder, CO

Comment on SpreadSheet::ParseExcel Failure to Match Question
Select or Download Code
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};
    Hope this helps,
    Darren :)
      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

      #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
        In that case, I would insert:
        print "==$sheet->{Cells}[$row][3]->{Val}==\n";
        in the appropriate place, so that you can see what the contents of that cell actually are.

        Cheers,
        Darren :)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://761609]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2014-04-21 01:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (489 votes), past polls