Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:
#!/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 (You'll need to add your path information here +based on where you put the attached spreadsheet.) my $file = "1001894004.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"; #locate columns in the spreadsheet from which we want to extract data my $sheet =$workbook->{Worksheet}[0]; #iterate through spreadsheet rows and extract site.siteid, site.name, +site.address, site.city, site.state, site.zip $de0 = $sheet->{Cells}[4][10]->{Val}; @de = split / /, $de0; ($de, $version, $den, $of, $den2) = @de; if ($sheet->{Cells}[7][11]->{Val} eq "") {$so=0} else {$so = $she +et->{Cells}[7][11]->{Val};} if ($sheet->{Cells}[11][12]->{Val} eq "") {$op=0} else {$op = $sh +eet->{Cells}[11][12]->{Val};} $dedate = $sheet->{Cells}[6][10]->{Val}; #my problem area starts here foreach my $row ($sheet->{MinRow}+18 .. $sheet->{MaxRow}) { if ( $sheet->{Cells}[$row][2]->{Val} = "SYSTEM" ) { #I am trying +to step through each row until I encounter the string "SYSTEM" in the + 3rd cell (identified as column 2 in the module). It looks like I get + a hit because the output indicates that the rest of the iterations a +re taking place. $comment = $sheet->{Cells}[$row-2][2]->{Val}; #however, the $comme +nt variable is getting feed with the string "SYSTEM" instead of the l +onger string in the cell two rows above. I am attempting to pull that + value with my $row-2 pointer. That approach has worked well in other + scripts so I am not sure why it is failing here. 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}; # another strange outco +me is the $model variable is also getting filled with "SYSTEM" instea +d of the value in the current row. That worked properly until I added + the if "SYSTEM" statement. $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}; $repfl = $sheet->{Cells}[$row][8]->{Val}; $dmfl = $sheet->{Cells}[$row][9]->{Val}; $cost = $sheet->{Cells}[$row][10]->{Val}; $marg = $sheet->{Cells}[$row][11]->{Val}; $maint = $sheet->{Cells}[$row][12]->{Val}; if ($sheet->{Cells}[$row][16]->{Val} eq "") {$upgrade=0} else {$u +pgrade = $sheet->{Cells}[$row][16]->{Val};} if ($sheet->{Cells}[$row][17]->{Val} eq "") {$bu=0} 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, +deal,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,$m +aint,\'$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;
Prepared By: rep Quote Name: IT N +AS Refresh Celerras NS80 Only Configs Quote Status: Draft Quote #: 1001 +894004 Version 17 of 17 Operating Unit: Approved Date: + Catalyst Order Type: System Date: + 05-Feb-2009 GSA Deal? Contract: Customer Selling Relationship: + Sold To: Bill To: Ship To: Install: + Oppty name: IT SJ Celerra Refresh Customer Customer Customer Op +pty #: 0 0 0 0 Customer Agreement: PO #: Enduser PO#: Line # QTY Product ID Description Units "Total List Pri +ce (USD)" Discount "Total Deal Price (USD)" "REP Floor (USD)" "DM Floor (USD)" "Cost (USD)" Margin "Maint LP Annual (USD)" IT SJC-12 NAS Refesh NS80 12TB Mirrored Usable (sjc12-0324/185 +3/3402) SYSTEM PSI FOR NS80 1 1 NS80CDME65 NS80 INT ENCL WITH 2 XB65 FACTORY EA 0 + 0% 0 0 0 0 0.0% 0 2 1 RACK-40U-60 40U COMMON RACK - 4 PDP EA 0 0% +0 0 0 0 0.0% 0 3 7 NS-4PDAE NS INT EXP DAE EA 0 0% 0 0 +0 0 0.0% 0 4 1 NS80-CS NS80 CONTROL STATION FACTORY PRIMARY EA 0 + 0% 0 0 0 0 0.0% 0 5 1 NS80-CS2 NS80 CONTROL STATION FACTORY 2ND EA 0 + 0% 0 0 0 0 0.0% 0 6 1 NS80-DME0 NS80 ENCLOSURE FACTORY EA 0 0% 0 + 0 0 0 0.0% 0 7 1 NS80-AUX NS80 ARRAY RACK REQ EA 0 0% 0 0 + 0 0 0.0% 0 8 2 NS80CDM1-4C NS80 XB65: 4GB4GB MIM;6CU+2OP GIGE+1X10 GIGE +FAC EA 0 0% 0 0 0 0 0.0% 0 9 1 V-NS807315K VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5 +EA 0 0% 0 0 0 0 0.0% 0 10 90 NS-4G10-300 300GB 10K 520BPS 5/12V 4GB FC 16MB -R EA + 0 0% 0 0 0 0 0.0% 0 11 4 NS-4G10-300HS 300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS + EA 0 0% 0 0 0 0 0.0% 0 12 1 PW40U-60-US RACK-40U-60 PWR CORD US EA 0 0% + 0 0 0 0 0.0% 0 13 1 NS80-DCD DOC & CD: NS80 EA 0 0% 0 0 + 0 0 0.0% 0 14 2 MODEM-US UNITED STATES MODEM EA 0 0% 0 +0 0 0 0.0% 0 Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 1 3 NS80C-CIFS-L CELERRA NS80C CIFS LIC EA 0 0% +0 0 0 0 0.0% 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Configuration Sub-total + Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Services Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% Maintenance and Warranty Upgrade Sub-total $ 0 +0% $ 0 Configuration Total $ 0 0% $ 0 $ 0 $ + 0 $ 0 0.0% $ 0 IT SJC-K NAS Refresh CCO NS80 1 of 3 3TB Mirrored Usable (sjck +-nsg2268) SYSTEM PSI FOR NS80 1 1 NS80CDME65 NS80 INT ENCL WITH 2 XB65 FACTORY EA 0 + 0% 0 0 0 0 0.0% 0 2 1 RACK-40U-60 40U COMMON RACK - 4 PDP EA 0 0% +0 0 0 0 0.0% 0 3 2 NS-4PDAE NS INT EXP DAE EA 0 0% 0 0 +0 0 0.0% 0 4 1 NS80-CS NS80 CONTROL STATION FACTORY PRIMARY EA 0 + 0% 0 0 0 0 0.0% 0 5 1 NS80-CS2 NS80 CONTROL STATION FACTORY 2ND EA 0 + 0% 0 0 0 0 0.0% 0 6 1 NS80-DME0 NS80 ENCLOSURE FACTORY EA 0 0% 0 + 0 0 0 0.0% 0 7 1 NS80-AUX NS80 ARRAY RACK REQ EA 0 0% 0 0 + 0 0 0.0% 0 8 1 V-NS807315K VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5 +EA 0 0% 0 0 0 0 0.0% 0 9 24 NS-4G10-300 300GB 10K 520BPS 5/12V 4GB FC 16MB -R EA + 0 0% 0 0 0 0 0.0% 0 10 1 NS-4G10-300HS 300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS + EA 0 0% 0 0 0 0 0.0% 0 11 1 PW40U-60-US RACK-40U-60 PWR CORD US EA 0 0% + 0 0 0 0 0.0% 0 12 1 NS80-DCD DOC & CD: NS80 EA 0 0% 0 0 + 0 0 0.0% 0 13 2 MODEM-US UNITED STATES MODEM EA 0 0% 0 +0 0 0 0.0% 0 Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 1 1 NS80C-CIFS-L CELERRA NS80C CIFS LIC EA 0 0% +0 0 0 0 0.0% 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Configuration Sub-total + Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Services Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% Maintenance and Warranty Upgrade Sub-total $ 0 +0% $ 0 Configuration Total $ 0 0% $ 0 $ 0 $ + 0 $ 0 0.0% $ 0 IT SJC-K NAS Refresh CCO NS80 2 of 3 3TB Mirrored Usable (sjck +-nsg1735) SYSTEM PSI FOR NS80 1 1 NS80CDME65 NS80 INT ENCL WITH 2 XB65 FACTORY EA 0 + 0% 0 0 0 0 0.0% 0 2 1 RACK-40U-60 40U COMMON RACK - 4 PDP EA 0 0% +0 0 0 0 0.0% 0 3 2 NS-4PDAE NS INT EXP DAE EA 0 0% 0 0 +0 0 0.0% 0 4 1 NS80-CS NS80 CONTROL STATION FACTORY PRIMARY EA 0 + 0% 0 0 0 0 0.0% 0 5 1 NS80-CS2 NS80 CONTROL STATION FACTORY 2ND EA 0 + 0% 0 0 0 0 0.0% 0 6 1 NS80-DME0 NS80 ENCLOSURE FACTORY EA 0 0% 0 + 0 0 0 0.0% 0 7 1 NS80-AUX NS80 ARRAY RACK REQ EA 0 0% 0 0 + 0 0 0.0% 0 8 1 V-NS807315K VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5 +EA 0 0% 0 0 0 0 0.0% 0 9 24 NS-4G10-300 300GB 10K 520BPS 5/12V 4GB FC 16MB -R EA + 0 0% 0 0 0 0 0.0% 0 10 1 NS-4G10-300HS 300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS + EA 0 0% 0 0 0 0 0.0% 0 11 1 PW40U-60-US RACK-40U-60 PWR CORD US EA 0 0% + 0 0 0 0 0.0% 0 12 1 NS80-DCD DOC & CD: NS80 EA 0 0% 0 0 + 0 0 0.0% 0 13 2 MODEM-US UNITED STATES MODEM EA 0 0% 0 +0 0 0 0.0% 0 Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 1 1 NS80C-CIFS-L CELERRA NS80C CIFS LIC EA 0 0% +0 0 0 0 0.0% 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Configuration Sub-total + Hardware Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Software Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% $ 0 Services Sub-total $ 0 0% $ 0 $ 0 $ +0 $ 0 0.0% Maintenance and Warranty Upgrade Sub-total $ 0 +0% $ 0 Configuration Total $ 0 0% $ 0 $ 0 $ + 0 $ 0 0.0% $ 0 Note: Booking Value includes Professional Services, the MISC model, and book +ing redirects. Software Content Percent calculation is ((SW/(HW+SW))*100. + Gross Cost and Gross Margin calculations include Professional Services +, but exclude Maintenance and the MISC model.
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: Variable Value Confusion
by toolic (Bishop) on Mar 11, 2009 at 22:44 UTC | |
by finhagen (Sexton) on Mar 12, 2009 at 22:37 UTC |