Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:


I have a spreadsheet I wish to parse via Spreadsheet::ParseExcel. For the most part my parsing script works very well but I want to capture some additional information and need help with the best approach.

The spreadsheet is the output of my company's order entry application. Each device we sell grouped by a Device description cell followed by 5 to 15 lines of part numbers. The key word identifying a new device is "SYSTEM" which occurs in a specific column (2). I can locate that cell with a if statement and that works. What I want to do is iterate through the spreadsheet rows (currently a foreach statement) until I find the SYSTEM cell, then capture the part number rows. I can do that much already. However, I want to continue to capture the part number rows UNTIL I reach the next SYSTEM header and then start the process over. My goal is to capture the SYSTEM header or description and add it to the part number rows as I insert it into a mysql database. Here is how things look at a high level: 

Device description cell

SYSTEM

Part 1
Part 2
Part 3
Part 4

Device description cell

SYSTEM

Part 1
Part 2
Part 3
Part 4

Device description cell

SYSTEM

Part 1
Part 2
Part 3
Part 4

[download]
I would think I would call a subroutine which calls itself agian each time it encounters the SYSTEM key word. But I am not sure what kind of iterative loop would work best in this case. I am hoping the kind and wise Perl Monks can recommend the statement structure that would work best to address my objective.

Thank you!

Hagen

Boulder, CO

Replies are listed 'Best First'.
Re: Iterative Subroutine Approach Question
by ELISHEVA (Prior) on Mar 12, 2009 at 11:41 UTC

    This question appears to be a continuation of the question you posted earlier: Variable Value Confusion. I think you might get better help by adding the above to your original post. Otherwise people might not realize that you made an attempt at your own code. If the name of the original post bothers you, you can change the title over there to the title you are using here. Double posting to create a new title is unnecessary and frowned upon.

    Also toolic suggested there that you replace = with eq, did that work?

    Please answer on your original post (as a reply to toolic answer) so that people can see the whole history - not here. You are likely to get much better help that way.

    Best, beth

[reply]
[d/l]
[select]
Re: Iterative Subroutine Approach Question
by bellaire (Hermit) on Mar 12, 2009 at 11:39 UTC
    You probably want to use a simple foreach like you are doing. It sounds like you've got the hard parts figured out. Basically, in your loop, first use your if statement to see whether your row contains a SYSTEM header. If it does, set a variable to contain the description which appeared in the row above. Something like this pseudocode: 
    
my $current_description;
foreach $row (@rows) {
    if ($row is a 'SYSTEM' row) {
        $current_description = (description from 2 rows above);
    }
    if ($row is a part number) {
        insert into database ($part_number, $current_description)
    }
}

    [download]
    That way, you're just inserting all the parts and descriptions, and all you do is change your description whenever you hit a SYSTEM row. If you need more information than just a description, you can use a hash structure instead of a scalar, but the concept is the same.

    UPDATE: Argh, another double post! As ELISHEVA says below, go back to your original post and look at the answers there. It's not nice to have me spend my time helping you when I don't know that others have already given you good advice on the same topic.
[reply]
[d/l]
      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.
      Read more... (18 kB)
[reply]
[d/l]
[select]
        With regards to closing out your original foreach loop and starting a new one, that's exactly what I don't think you should do. It's possible, but it makes your code more complicated than it needs to be.

        In the code you've given, you've got two separate foreach loops, each one having internal if-structures. Simply move all of the if-structures into a single large foreach loop, i.e.: 
        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};
    if ( $sheet->{Cells}[$rw][0]->{Val} =~ /(^\d+)/ ) {
    
     $line = $sheet->{Cells}[$rw][0]->{Val};
     $qty = $sheet->{Cells}[$rw][1]->{Val};
     ...

        [download]
        Doing that should make sure that $comment is set appropriately each time you get to a detail line, without the need for any more than the single, large foreach loop.
[reply]
[d/l]

Back to Seekers of Perl Wisdom