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.",,,,,,,,,,,,,, ,,,,,,,,,,,,,,

In reply to Re^2: Iterative Subroutine Approach Question by finhagen
in thread Iterative Subroutine Approach Question by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.