in reply to excel to hash

Hello colox,

you got already two useful answers. What I can add is that Spreadsheet::ParseExcel (the underliyng module as haukex suggested) does not supports .xlsx files. Error checking, as wisely proposed by NetWallah would probably addressed you in rigth direction.

You can process such files using Spreadsheet::XLSX Spreadsheet::ParseXLSX (see below) but abandoning the use of Spreadsheet::ExcelHashTable but I think that get all the content into an hash will not be so difficult.

L*

There are no rules, there are no thumbs..
Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.

Replies are listed 'Best First'.
Re^2: excel to hash
by Tux (Canon) on Sep 14, 2017 at 06:29 UTC
[reply]
Re^2: excel to hash
by colox (Sexton) on Sep 13, 2017 at 16:43 UTC
    thank you so much for your inputs. i tried to save the spreadsheet into older 2003 XLS & the error is gone. Below is the hash output using Dumper: 
    $VAR1 = \{
            '001' => {
                        'Name' => 'AAA'
                      },
            '002' => {
                        'Name' => 'BBB'
                      }
          };

    [download]
    My question now is, how I can correctly assign the keys '001', '002', to the correct values 'AAA', 'BBB', respectively?
[reply]
[d/l]
      See my above answer about .xls vs .xlsx files. What are you asking now is a simple hash operation: you must assign to each key the inner contained value:

                                     # quotes needed even with fat comma or 
+you get 1 instead of 001
perl -MData::Dumper -e "%hash=('001'=>{Name=>'AAA'}); foreach $k (keys
+ %hash){$hash{$k}=$hash{$k}{Name}} print Dumper \%hash"

$VAR1 = {
          '001' => 'AAA'
        };

      [download]

      L*

      There are no rules, there are no thumbs..
      Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
[reply]
[d/l]
[select]

      Hi, at a complete guess I would try changing your call to use the 'Name' column as the key of the hash. Untested; see what you get: 

      $excel_table->parse_table("Sheet1", "Name") ;

      [download]
      ... or, if that doesn't work, you can manipulate the hash you already have: 
      my $hashref = $excel_table->get_table("ID");
my %excel_hash = %{ $hashref };
my %new_hash = map { $_ => $excel_hash{ $_ }->{'Name'} } keys %excel_h
+ash;

      [download]
      (map loops throuugh the keys of the hash, assigning each key to $_. In the block we dig down and get the value we want and match it to the key in the new hash we create).

      Hope this helps!


      The way forward always starts with a minimal test.
[reply]
[d/l]
[select]
        Hi lnickt, thanks much!... but i apologize if i still not able to get it resolved... been frying my brain on the correct syntax to extract the hash value but im having issue using the correct variable in terms of scalar, etc. 
        foreach my $keys1 (keys %{$excel_hash}) {
    print "$keys1\n";
    foreach my $keys2 (keys %{%$excel_hash{$keys1}}) {
    print "$keys2\n";
    my $value = %{%$excel_hash{$keys1}{$keys2}};
    }
    }

        [download]
        From the above code, everything works without the line where i assign the value to $value. Output below: 
        $VAR1 = \{
            '002' => {
                       'Name' => 'BBB'
                     },
            '001' => {
                       'Name' => 'AAA'
                     }
          };
002
Name
001
Name

        [download]
        With that $value line, i have syntax error on that line. Please help...
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom