Re^2: Iterative Subroutine Approach Question

[finhagen]

Bellaire, Thank you for your advice. The issue I was trying to address was with the same script but a different issue. My aim was to avoid posing too complex a set of requests but I can see how that might create confusion. My apologies to the perl monk community.

With regard to my previous post, I will reply within that post but the "eq" suggestion did not resolve my problem but I discovered I could move my statement into its own foreach loop and that addressed the specific issue I posed there. That had the interesting result that only the last SYSTEM keyword and associated current_description was appended to the row during the insert. So even though there were 6 SYSTEM definitions the foreach loop went through them all and ended up only returning the last one in the spreadsheet. That is what has prompted the question about an iterative subroutine approach.

Regarding your recommendation, per my result above, my concern is I need to update the $current_description variable each time the foreach loop hits a new "SYSTEM" row. I may be exposing my relative inexperience but I don't know how I refresh the $current_description with the new '2 rows above' associated with the new SYSTEM value? How do I close out the original foreach loop and start a new one? I was thinking I could create a subroutine which I would trigger to restart each time it hit the "SYSTEM" key word. However, I am not sure how to start and stop that subroutine.

Is there a way to upload the spreadsheet I am parsing. I am sure that would be helpful but I don't see how I can do that.

Hagen

Here is the code I have that returns the last $current_description from the last SYSTEM definition.

#!/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 = "/QAR1001894004v17.xls";

#global variables

our $row;
our $rw;
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};
# the new foreach loop that returns the last SYSTEM and $comment in th
+e spreadsheet (not exactly what I was hoping for)
    
foreach my $row ($sheet->{MinRow}+18..$sheet->{MaxRow}){

    if ($sheet->{Cells}[$row][2]->{Val} eq "SYSTEM") {$comment = $shee
+t->{Cells}[$row-2][2]->{Val};}
    if ($sheet->{Cells}[$row][3]->{Val} eq "UPGRADE SOLUTION") {$upgra
+de=1} else {$upgrade=0};
}

foreach my $rw ($sheet->{MinRow}+18 .. $sheet->{MaxRow}) {    
    
    if ( $sheet->{Cells}[$rw][0]->{Val} =~ /(^\d+)/ ) {
    
     $line = $sheet->{Cells}[$rw][0]->{Val};
     $qty = $sheet->{Cells}[$rw][1]->{Val};
     $model = $sheet->{Cells}[$rw][2]->{Val};
     $mdesc = $sheet->{Cells}[$rw][3]->{Val};
     $unit = $sheet->{Cells}[$rw][4]->{Val};
     $listp = $sheet->{Cells}[$rw][5]->{Val};
     $discp = $sheet->{Cells}[$rw][6]->{Val};
     $deal = $sheet->{Cells}[$rw][7]->{Val};
     $repfl = $sheet->{Cells}[$rw][8]->{Val};
     $dmfl = $sheet->{Cells}[$rw][9]->{Val};
     $cost = $sheet->{Cells}[$rw][10]->{Val};
     $marg = $sheet->{Cells}[$rw][11]->{Val};
     $maint = $sheet->{Cells}[$rw][12]->{Val};
     if ($sheet->{Cells}[$rw][17]->{Val} eq "") {$bu=0} else {$bu = $s
+heet->{Cells}[$rw][17]->{Val};}
     if ($sheet->{Cells}[$rw][18]->{Val} eq "") {$dealn=0} else {$deal
+n= $sheet->{Cells}[$rw][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;

Here is a csv version of the spreadsheet I am trying parse:

,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,Prepared By:,rep,,Quote Name:,IT NAS Refresh Celerras NS80 Only 
+Configs,,,,
,,,,,,Quote Status:,Draft,,Quote #:,1001894004 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,,,,,,,Oppty #:,0,LINKED,
0,,,0,0,,,,,,,Customer Agreement:,,,
,,,,,,,,,,,PO #:,,,
,,,,,,,,,,,Enduser PO#:,,,
,,,,,,,,,,,,,,
Line #,QTY,Product ID,Description,Units,"Total List Price
(USD)",Discount,"Total Deal Price
(USD)","REP Floor
(USD)","DM Floor
(USD)","Cost
(USD)",Margin,"Maint LP Annual
(USD)",,Floor Break
,,,,,,,,,,,,,,
,,IT SJC-12 NAS Refesh NS80 12TB Mirrored Usable (sjc12-0324/1853/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 ,,0 
2,1,RACK-40U-60,40U COMMON RACK - 4 PDP,EA,0 ,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 ,,0 
4,1,NS80-CS,NS80 CONTROL STATION FACTORY  PRIMARY,EA,0 ,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 ,,0 
6,1,NS80-DME0,NS80 ENCLOSURE FACTORY,EA,0 ,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 ,,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 ,,0 
9,1,V-NS807315K,VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5,EA,0 ,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 ,,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 ,,0 
12,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,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 ,,0 
14,2,MODEM-US,UNITED STATES MODEM,EA,0 ,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 ,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-nsg22
+68),,,,,,,,,,,,
,,,,,,,,,,,,,,
,,SYSTEM,PSI FOR NS80,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80CDME65,NS80 INT ENCL WITH 2 XB65 FACTORY,EA,0 ,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 ,,
+0 
3,2,NS-4PDAE,NS INT EXP DAE,EA,0 ,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 ,,0 
5,1,NS80-CS2,NS80 CONTROL STATION FACTORY  2ND,EA,0 ,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 ,,0 
7,1,NS80-AUX,NS80 ARRAY RACK REQ,EA,0 ,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 ,,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 ,,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 ,,0 
11,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,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 ,,0 
13,2,MODEM-US,UNITED STATES MODEM,EA,0 ,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 ,,
+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-nsg17
+35),,,,,,,,,,,,
,,,,,,,,,,,,,,
,,SYSTEM,PSI FOR NS80,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80CDME65,NS80 INT ENCL WITH 2 XB65 FACTORY,EA,0 ,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 ,,
+0 
3,2,NS-4PDAE,NS INT EXP DAE,EA,0 ,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 ,,0 
5,1,NS80-CS2,NS80 CONTROL STATION FACTORY  2ND,EA,0 ,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 ,,0 
7,1,NS80-AUX,NS80 ARRAY RACK REQ,EA,0 ,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 ,,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 ,,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 ,,0 
11,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,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 ,,0 
13,2,MODEM-US,UNITED STATES MODEM,EA,0 ,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 ,,
+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 boo
+king redirects.",,,,,,,,,,,,,,
Software Content Percent calculation is ((SW/(HW+SW))*100.,,,,,,,,,,,,
+,,
"Gross Cost and Gross Margin calculations include Professional Service
+s, but exclude Maintenance and the MISC model.",,,,,,,,,,,,,,
"Field Product Cost and Field Product Margin calculations exclude Prof
+essional Services, Maintenance and the MISC model.",,,,,,,,,,,,,,
,,,,,,,,,,,,,,