Re: excel to hash

[Discipulus]

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*

[Tux]

Please do not use or promote Spreadsheet::XLSX. It did serve its purpose very well, but it is unmaintaned, buggy and has now been deprecated in favor of Spreadsheet::ParseXLSX, which is well maintaned and resembles the Spreadsheet::ParseExcel API.

[colox]

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

My question now is, how I can correctly assign the keys '001', '002', to the correct values 'AAA', 'BBB', respectively?

[Discipulus]

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

L*

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