Re^3: excel to hash

[1nickt]

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") ;

... 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;

(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!

[colox]

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}};
    }
    }

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

With that $value line, i have syntax error on that line. Please help...

[colox]

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";
    }
}

output:

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

[1nickt]

Hi again,

This:

%{%$excel_hash{$keys1} }

is redundant.

Work from the inside.

• You start in that expression with a reference to a hash: $excel_hash
• Then you dereference it into a hash with %$excel_hash (better written as %{ $excel_hash }
• Then you get the value of one of the keys in the hash (with the key name held in the scalar $keys1), using %$excel_hash{$keys1} (somewhat better written as %{ $excel_hash }{ $keys1 }.)
• Then you take the hash that is returned by what you got so far, and take a reference to it, by enclosing in braces: { %$excel_hash{$keys1} }
• Finally, you dereference the new reference you made, to get back to two steps earlier, with %{%$excel_hash{$keys1} }

Let's start again. Say you have a hash reference  $excel_href. (Note you should name it with 'href' since it is not a hash, but a scalar that holds a reference to the hash.) Now you want to loop through the keys, so make it easy on yourself and deference the hashref at the start.

my %excel_hash = %{ $excel_href };

Loop through the keys, using for, which is the same as for each:

  
for my $key ( keys %excel_hash ) {
    ...
}

So now you have the key, and you need the value, and we know that the value is going to be a "sub-hash", ie a scalar holding a reference to another hash. Since we're lazy and we don;t like typing arrows, and for consistency, we can dereference this hashref, too, and we'll do it right after we fetch the value:

for my $key ( keys %excel_hash ) {
    my $inner_href = $excel_hash{ $key };
    my %inner_hash = %{ $inner_href };
}

Finally, you want to get the value of the key named 'Name' in the inner hash.

for my $key ( keys %excel_hash ) {
    my $inner_href = $excel_hash{ $key };
    my %inner_hash = %{ $inner_href };
    my $name = $inner_hash{'Name'};
}

Or, for a version more similar to yours:

for my $key1 ( sort keys %{ $excel_href } ) {
    while ( my ( $key2, $val2 ) = each %{ $excel_href->{ $key1 } } ) {
        print "$key2 = $value \n";
    }
}

Or, for a version sticking to simple for loops (each should be handled with care):

for my $outer_key ( keys %{ $excel_href } ) {
    print "$outer_key:";
    for my $inner_key ( keys %{ $excel_href->{ $outer_key } } ) {
        print "\t$inner_key = $excel_href->{ $outer_key }->{ $inner_ke
+y }\n";
    }
}

Or, for the convenient way using map (assuming you know the name of the inner key you are trying to read):

print "$_\n" for map {"$_: Name = $excel_href->{ $_ }->{'Name'}"} keys
+ %{ $excel_href };

I expect all this is clear as mud at first reading, but have a play around and it should start making sense. Post back here with any questions.