Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re^3: excel to hash

by 1nickt (Monsignor)
on Sep 13, 2017 at 17:08 UTC ( #1199338=note: print w/replies, xml ) Need Help??

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

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!

The way forward always starts with a minimal test.

Replies are listed 'Best First'.
Re^4: excel to hash
by colox (Sexton) on Sep 13, 2017 at 18:19 UTC
    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...

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


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

        Hi again,


        %{%$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.

        The way forward always starts with a minimal test.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1199338]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2018-06-20 23:31 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (117 votes). Check out past polls.