How to parse excel files having multiple tables with different number of elements?

ankit.tayal560 has asked for the wisdom of the Perl Monks concerning the following question:

I've written this code to parse excel files and convert them into hash structure.

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

my $s_name;
my $s_name2;
my $attribute2;
my $attribute;

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_name;
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 $cell=$worksheet->get_cell($row,$col);
    next unless $cell;
    my $value=$cell->value();
    $student_name{$super_key}->{$key}=$value;
    }
}


foreach $s_name(sort keys %student_name)
{
    foreach $attribute (keys%{$student_name{$s_name}})
    {
        if($student_name{$s_name}{$attribute} eq "x")
        {
        print("$s_name-->$attribute\n");
        }
    }
print("\n");
}




my %p_name;

for my $row(1..$worksheet->row_range)
{
my $super_key2=$worksheet->get_cell($row,6)->value;
    for my $col(6..10)
    {
    my $key2=$worksheet->get_cell(0,$col)->value;
    my $cell2=$worksheet->get_cell($row,$col);
    next unless $cell2;
    my $value2=$cell2->value();
    $p_name{$super_key2}->{$key2}=$value2;
    }
}

foreach $s_name2(sort keys %p_name)
{
    foreach $attribute2 (keys%{$p_name{$s_name2}})
    {
        if($p_name{$s_name2}{$attribute2} eq "x")
        {
        print("$s_name2-->$attribute2\n");
        }
    }
print("\n");
}
[download]

EXCEL FILE 1 :
S.Name   IQ    Sports    P.Name   IQ    sports
Vishal    x       x     Mr. Jha    x     x
Jasen     x      x      Mr. kumar  x     x



EXCEL FILE 2 :

S.Name   IQ    Sports    P.Name   IQ    sports
Vishal    x       x     Mr. Jha    x     x
Jasen     x      x      Mr. kumar  x     x
Garry     x      x
[download]

The problem is that my code works fine for Excel file 1 but for Excel file 2 it gives me an error

ERROR :
can't call method "value" on an undefined value
[download]

Can someone explain me the problem and suggest me how to approach to solve it?

Comment on How to parse excel files having multiple tables with different number of elements? Select or Download Code

Replies are listed 'Best First'.
Re: How to parse excel files having multiple tables with different number of elements? by marto (Cardinal) on Oct 12, 2016 at 12:55 UTC
This is the same error as in your previous question, and you didn't even give us the full error message which tells you which line of code to start looking at: `D:\junk>perl xl.pl Garry--> Sports Jasen--> Sports Can't call method "value" on an undefined value at xl.pl line 55.` [download] Since we've been round this loop before, I suggest you take a look at the line of code in question and the actual data stored within Excel, the documentation for the `get_cell`, row/col_range methods and the previous thread explaining the problem.	[reply] [d/l] [select]
Re: How to parse excel files having multiple tables with different number of elements? by choroba (Cardinal) on Oct 12, 2016 at 11:50 UTC
How is this different to How to convert Excel contents into a hash table and further access the contents of that hash table ?, where several solutions were proposed? ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l]
Re: How to parse excel files having multiple tables with different number of elements? by Phenomanan (Monk) on Oct 12, 2016 at 13:49 UTC
This was a simple mistake. Look here: `for my $col(0..4) { my $key=$worksheet->get_cell(0,$col)->value; my $cell=$worksheet->get_cell($row,$col); next unless $cell; my $value=$cell->value();` [download] The blank cells in Garry's row are causing the error, meaning that your 'next unless $cell' statement is not skipping the empty cells. Maybe try: `for my $col(0..4) { my $key=$worksheet->get_cell(0,$col)->value; my $cell=$worksheet->get_cell($row,$col); unless($cell){ # Null value } else{ my $value=$cell->value(); ... }` [download] I tested this and it worked for me.	[reply] [d/l] [select]