in reply to Re: excel to hash
in thread excel to hash

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?

Replies are listed 'Best First'.
Re^3: excel to hash
by Discipulus (Canon) on Sep 13, 2017 at 17:05 UTC
    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]
Re^3: excel to hash
by 1nickt (Canon) on Sep 13, 2017 at 17:08 UTC

    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]

        Hi, got it working with below: 

        foreach my $keys1 (sort keys %{$excel_hash}) {
    while (my ($key2, $value) = each %{%$excel_hash{$keys1} } ) {
        print "$key2 = $value \n";
    }
}

        [download]

        output: 

        $VAR1 = \{
            '002' => {
                       'Name' => 'BBB'
                     },
            '001' => {
                       'Name' => 'AAA'
                     }
          };
Name = AAA
Name = BBB

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

In Section Seekers of Perl Wisdom