Re^2: How to convert Excel contents into a hash table and further access the contents of that hash table ?

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
use warnings;

my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse('C:\Perl\perl_tests\Sample.xls');
if(!defined $workbook)
{
die $parser->error(),".\n";
}

my $worksheet=$workbook->worksheet('Sheet1');
my %student_data;

for my $row(1..$worksheet->row_range)
{
my $super_key=$worksheet->get_cell($row,0)->value;
    for my $col(0..4)
    {
    my $key=$worksheet->get_cell(0,$col)->value;
    my $value=$worksheet->get_cell($row,$col)->value;
##here I am getting a problem##
    $student_data{$super_key}->{$key}=$value;
    }
}

foreach my $student_name(sort keys %student_data)
{
    foreach my $attribute (keys%{$student_data{$student_name}})
    {
        if($student_data{$student_name}{$attribute} eq "x")
        {
        print("$student_name-->$attribute\n");          }
    }
    print("\n");
}
[download]

it shows an error "can't call method 'value' on an undefined value. I guess it is because in some cells 'x' is not written and hence that cell is empty so it is showing me this error. I tried it when all the cells contain 'x' then it is working. What should I do in this case?

Comment on Re^2: How to convert Excel contents into a hash table and further access the contents of that hash table ? Download Code

Replies are listed 'Best First'.

Re^3: How to convert Excel contents into a hash table and further access the contents of that hash table ?
by Athanasius (Archbishop) on Oct 05, 2016 at 07:32 UTC

Hello ankit.tayal560,

Are you sure the error is coming from the code shown? When I run your code, accessing an Excel file created according to the table in the OP, it works without errors:

17:20 >perl 1704_SoPW.pl
Azhar-->Academic
Azhar-->IQ
Azhar-->Music
Azhar-->sports

Gurmeet-->Academic
Gurmeet-->IQ
Gurmeet-->Music
Gurmeet-->sports

Jason-->IQ
Jason-->Music

Vishal-->Academic
Vishal-->IQ
Vishal-->sports


17:21 >
[download]

It may help if you detail your platform, Perl version, and Spreadsheet::ParseExcel version. Here are mine:

Windows 8.1, 64-bit
Strawberry Perl 5.22.1
Spreadsheet::ParseExcel 0.65

Also, please copy and paste the exact error message you receive (within <code> ... </code> tags).

Cheers,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

Re^4: How to convert Excel contents into a hash table and further access the contents of that hash table ?

by ankit.tayal560 (Beadle) on Oct 05, 2016 at 08:12 UTC

Yes error is coming when I run the above code, plateform which I am using is : (1) Windows 7 (2) Active state perl 5.8.9 build 825 (3) Spreadsheet::ParseExcel 0.65

exact error message is :
Can't call method "value" on an undefined value at C:\perl\perl_tests\
+excel_parser.pl line 22
[download]

[reply]
[d/l]

Re^5: How to convert Excel contents into a hash table and further access the contents of that hash table ?

by Athanasius (Archbishop) on Oct 05, 2016 at 12:46 UTC

Well, you have the latest version of Spreadsheet::ParseExcel but a very old version of Perl. The oldest version I have on my computer at the moment is Strawberry Perl v5.12.3 (32 bit), which also runs the code you posted with no errors or warnings.

The best suggestion I can make is that you check your Excel file. If I add a blank line above the header — i.e., the line containing:

Student name  IQ  sports  Music  Academic
[download]

— then I get the error message you reported.

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]

Re^3: How to convert Excel contents into a hash table and further access the contents of that hash table ?
by Tux (Canon) on Oct 05, 2016 at 09:14 UTC

for my $row (1 .. $worksheet->row_range) {
    my $super_key = $worksheet->get_cell ($row, 0)->value;
    for my $col (0 .. 4) {
        my $key   = $worksheet->get_cell (0,    $col)->value;
        my $value = $worksheet->get_cell ($row, $col)->value;
        # here I am getting a problem #
        $student_data{$super_key}->{$key} = $value;
        }
    }
[download]

I am pretty sure the error if from either of the two lines above that. get_cell (...) will return undef for undefined cells, for which you cannot use the value method.

Enjoy, Have FUN! H.Merijn

[reply]
[d/l]
[select]