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

I have an excel document that I read through using Spreadsheet::ParseExcel and put all the data into a hash like this: 
 $excel{$tab."-".$row."-".$col} = $cell->{Val};

[download]
I would like to be able to search through the hash, looking for conditionals. For example, on the customer tab in the workbook, if column 1 has a value in it, I want to iterate through the entire row cell by cell so I can write the data a certain way (new customer). If there is a value in that column, I will write that one value and move onto the next sheet in the workbook. What's a good way to do this, or a better way to store my data in a hash and produce good results? I was starting off like this, but it sorts on the first character, as expected, but was thinking it would be quicker if I had a hash of hashes of sorts, and sorted each level of the hash. 

foreach my $key (sort {lc($a) cmp lc($b)} keys %excel) {
        my($tab,$row,$col) = split(/-/, $key);
        if (($tab eq "Customers") && ($col eq "1") {
        
        }

[download]

Replies are listed 'Best First'.
Re: Searching through a hash
by ikegami (Patriarch) on Nov 05, 2007 at 20:03 UTC 
    $excel{$tab}{$row}{$col} = $cell->{Val};

...

foreach my $tab_name (keys %excel) {
   # Only intersted in "Customers" tab.
   next if $tab_name ne 'Customers';

   my $rows = $excel{$tab_name};

   foreach my $row_name (sort { $a <=> $b } keys %$rows) {
      my $cols = $rows->{$row_name};

      # Not interested in rows where the first cell is blank.
      next if !$cols->{A};

      ...[ Do something with %$cols ]...
   }
}

    [download]
[reply]
[d/l]
      Yeah that looks good, thanks! So with this %$cols, I have a question. When you put the % attached to $cols, is that telling perl that there's actually a hash stored in this variable, so let's treat it like that? I am going to do some XML manipulation when I figure out how to traverse the hash correctly. For testing right now, how would I iterate through the entire %$cols values, printing them out, and then jumping back up to the next $row when done. Thanks!
[reply]

        $cols contains a reference to a hash. %$cols (short for %{$cols}) means to access the hash referenced by the reference in $cols. See perlref. 

        my @sorted_col_names = sort { length($a) <=> length($b) || $a cmp $b }
                       keys %$cols;

foreach my $col_name (@sorted_col_names) {
   my $cell = $col->{$col_name};
   print("$cell\t");
}

print("\n");

        [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom