Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
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
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};
    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 contemplating the Monastery: (7)
As of 2015-07-30 04:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (270 votes), past polls