youhaveaBigEgo has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone,

This problem is driving me nuts.
It should be fairly straightforward, but something's wrong.
My .xls file *(not xlsx) has only few values: 

-35, -39, -39, -60, -35, -39, -39,
-36, -40, -40, -59, -36, -40, -40,

[download]

I installed Spreadsheet::Read using Perl Package Manager.
Not ppm install from command line, I used the GUI tool to install it.
My perl version is 5.18.2 (from ActiveState Perl)
(yes i know some people here may crib about it, and I'm on Win 7 OS as well. Chill)
Here is my script: 

use Spreadsheet::Read;
our $workbook = ReadData("tmp.xls", debug => 9);
my ($i,$j)=0;
my @cell;
for($i=1;$i<3;$i++){
    for($j=1;$j<7;$j++){
        $cell[$j] = cr2cell($j, $i); 
        print $workbook->[1]{$cell[$j]};
        print ",";
    }    
    print "\n";
}

[download]

This should have worked.
But instead of printing the values above in my sheet, I just get :
I added the debug flag to understand what's wrong 

$Options = {
             'debug' => 9,
             'strip' => 0,
             'rc' => 1,
             'cells' => 1,
             'dtfmt' => 'yyyy-mm-dd',
             'clip' => 1,
             'attr' => 0
           };
,,,,,,
,,,,,,

[download]

What gives?
What did I do wrong?
Did the PPM miss a dependency or something?
Weird thing is, script works fine on another PC (not mine),
but I don't know what all packages that station has installed in it.
The perl version is the same as mine.
Can anyone shine some light?
Thanks!

Replies are listed 'Best First'.
Re: Unable to read values from Excel file
by Athanasius (Archbishop) on Aug 20, 2016 at 06:05 UTC

    Hello youhaveaBigEgo,

    Check that Spreadsheet::ParseExcel is installed. When I run your script on Strawberry Perl v5.18.2 (on Windows 8.1), using Spreadsheet::Read 0.66, with debug => 5, it works correctly as follows:

    16:01 >perl 1684_SoPW.pl
$Options = {
             'attr' => 0,
             'debug' => 5,
             'strip' => 0,
             'clip' => 1,
             'cells' => 1,
             'rc' => 1,
             'dtfmt' => 'yyyy-mm-dd'
           };
Opening XLS tmp.xls using Spreadsheet::ParseExcel-0.65
        1 sheets
        Sheet 2 'Sheet1' 2 x 7
-35,-39,-39,-60,-35,-39,
-36,-40,-40,-59,-36,-40,

16:01 >

    [download]

    BTW, your C-style for loops would be better written as foreach loops:

    for my $i (1 .. 2) {
    for my $j (1 .. 6) {

    [download]

    Hope that helps,

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

[reply]
[d/l]
[select]
Re: Unable to read values from Excel file
by Marshall (Canon) on Aug 20, 2016 at 10:44 UTC
    I tested your code with Spreadsheet::Read 0.63 on my Win XP Active Perl 5.20 installation and it works fine. I did have Spreadsheet::ParseExcel already for other reasons. That may be the "trick". 
    $Options = {
             'dtfmt' => 'yyyy-mm-dd',
             'attr' => 0,
             'strip' => 0,
             'rc' => 1,
             'cells' => 1,
             'clip' => 1,
             'debug' => 5
           };
Opening XLS $txt
    1 sheets
    Sheet 2 'test' 2 x 7
-35,-39,-39,-60,-35,-39,
-36,-40,-40,-59,-36,-40,

    [download]
    I've been using a different reader, Spreadsheet::DataFromExcel for simple .XLS files (won't work for .XLSX), real easy to use, here is demo: 
    #!usr/bin/perl
use strict;
use warnings;

use Spreadsheet::DataFromExcel;
use Data::Dumper;
    
my $p = Spreadsheet::DataFromExcel->new;
    
my $data = $p->load('Test.xls', 'test',0) or die $p->error;

foreach my $rowref (@$data) #$data is ref to an Array of Array
{
   print join (',',@$rowref), "\n";
}

__END__
-35,-39,-39,-60,-35,-39,-39
-36,-40,-40,-59,-36,-40,-40

    [download]
[reply]
[d/l]
[select]
      With DataFromExcel , I am unable to do certain tasks that Spreadsheet::Read would give me , functions such as : cr2cell cell2cr row cellrow rows That said after installing Spreadsheet::ParseExcel I am able to get my result. Thanks you guys.
[reply]
        Great to hear back that you have a solution to your problem! I suspected that the odds were good given the ease at which I was able to run your code on my Active State machine. I've found the AS ppm (Perl Package Manager) to be quite good.

        Yes, DataFromExcel is a "one trick pony". It only has one trick (convert worksheet to Array of Array), but it does that one trick well. It doesn't have convenience functions like accessing a cell like "D4", you have to go to $data->[3,3]. I just needed 2 columns from one sheet of a 5 sheet Workbook and that was enough for me. Mileage varies. The trade-off between complexity and features happens all the time.

        Thanks++ for reporting that installing Spreadsheet::ParseExcel did what you needed. That feedback may help somebody else further down the road.

[reply]
[d/l]
      Thank-you, I will try it out if suggested recommendations here doesn't work.
[reply]
Re: Unable to read values from Excel file
by poj (Abbot) on Aug 20, 2016 at 11:15 UTC

    I get the same result as you if tmp.xls does not exist. Try adding a line to check.

    die $! unless -e 'tmp.xls';
our $workbook = ReadData("tmp.xls", debug => 9);

    [download]
    poj
[reply]
[d/l]
[select]
Re: Unable to read values from Excel file
by beech (Parson) on Aug 20, 2016 at 06:02 UTC

    Hi,

    What does $workbook contain? What is output of 

    use Data::Dump qw/ dd /;
dd( $workbook ) ;

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom