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

Hi there Monks!
The issue here is when reading this spreadsheet it has a title for the zip code, first name and last name columns
What I am trying to do is to ignore the column names read just the data and when there is no more data send the "DONE",
because I need to send it as a flag to another process. But here its seeing the column header first and sending the DONE, because
it does match on the “ELSE”. Is there a way to force the code to read the spreadsheet in reverse, that way I will know when I reach the end of it and then
send the DONE as flag, meaning that I reach the each of the file or ending of reading this spreadsheet?
#!/usr/bin/perl use strict; use warnings; use Spreadsheet::ParseExcel; use DateTime::Format::Excel; use Data::Dumper; ... foreach my $worksheet (@{$excel_sheet -> {Worksheet}}) { $worksheet -> {MaxRow} ||= $worksheet -> {MinRow}; # Skip worksheet if it doesn't contain data. next if $worksheet -> {MinRow} > $worksheet -> {MaxRow}; foreach my $row ($worksheet->{MinRow} .. $worksheet->{MaxRow}) { # Correct values or skip them: next unless defined $worksheet->{Cells}[$row][0]->{Val}; warn Dumper $worksheet->{Cells}[$row][0]->{Val}; if ( $worksheet->{Cells}[$row][0]->{Val} =~ /^\d{5}/ ) { my $xlsx_rows = $worksheet->{Cells}[$row]; # Send data send_data( zipcode => $xlsx_rows->[0]->{Val} || '', firstname => $xlsx_rows->[1]->{Val} || '', lastname => $xlsx_rows->[2]->{Val} || '', ); }else{ # Send flag as DONE once it reach the end of file or if there +is no data send_data( zipcode => 'DONE', # Flag ); } } } ...

The warn Dump printing results
DONE 01111 02334 09999

I need to be like this:
01111 02334 09999 DONE

Thanks for looking!

Replies are listed 'Best First'.
Re: Reading a spreadsheet
by Athanasius (Archbishop) on Feb 18, 2015 at 16:24 UTC

    No need to read in reverse, just move the contents of the else block to immediately after the foreach loop. The loop will end when the data is exhausted, and the if clause inside will ensure that only valid data is sent.

    Hope that helps,

    Athanasius <°(((><contra mundum Iustus alius egestas vitae, eros Piratica,

      Do you mean by remove the "ELSE" block and move its content immediately after the second foreach loop?

        Yes, exactly.

        (My eye must have skipped — I failed to register that there are two nested foreach loops, or I would have been clearer in my answer. It’s no excuse, but still ... indentation matters!)

        Athanasius <°(((><contra mundum Iustus alius egestas vitae, eros Piratica,

Re: Reading a spreadsheet
by runrig (Abbot) on Feb 18, 2015 at 16:23 UTC
    Why not skip the header and send the 'DONE' flag after you are 'DONE' reading the spreadsheet?

      Further re runrig's suggestion (well, I think it's "further" or a "detail"): see the Spreadsheet::ParseExcel documentation on row_range

      my ( $row_min, $row_max ) = $worksheet->row_range();

      ... or use the value of $row_min to skip the header line (and while your post doesn't say so explicitly, it does seem likely that the header line will consistently be in the first row... and that suggests another approach which won't add any code (and which is left as an exercise for the OP).