Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

excel to hash

by colox (Sexton)
on Sep 13, 2017 at 16:01 UTC ( #1199326=perlquestion: print w/replies, xml ) Need Help??
colox has asked for the wisdom of the Perl Monks concerning the following question:

dear perlmonks, I have below short code to read a simple excel (2010) table:

use strict; use warnings; use Spreadsheet::ExcelHashTable; my $file ='D:\\Scripts\\DeviceID_TableAssociation.xlsx'; print $file; my $excel_table = Spreadsheet::ExcelHashTable->new($file); $excel_table->parse_table("Sheet1", "ID") ; my $excel_hash = $excel_table->get_table("ID");

But im getting below error: Can't call method "worksheet" on an undefined value at C:/Strawberry/perl/site/lib/Spreadsheet/ line 49. Below is the table from excel:

----------------------- | ID | Name | ----------------------- | 001 | AAA | ----------------------- | 002 | BBB | -----------------------

Please let me know what am I missing or doing incorrectly. thank you all.

Replies are listed 'Best First'.
Re: excel to hash
by NetWallah (Canon) on Sep 13, 2017 at 16:32 UTC
    You need to add error-checking to your program.

    my $excel_table = Spreadsheet::ExcelHashTable->new($file) or die "Could not create excel_table object:" ; $excel_table->excel_checker("Sheet1", "ID") and die $excel_table->err +or();

                    All power corrupts, but we need electricity.

Re: excel to hash
by haukex (Abbot) on Sep 13, 2017 at 16:27 UTC
Re: excel to hash
by Discipulus (Monsignor) on Sep 13, 2017 at 16:39 UTC
    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.


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


        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.

        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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1199326]
Approved by haukex
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2018-05-27 15:52 GMT
Find Nodes?
    Voting Booth?