Hello Monks,

I am trying to use Spreadsheet::Read to parse a directory full of .xlsx documents that I generated from another Perl script. I need to extract the numeric values from column C in the second-to-last worksheet of each book, then use those values to pull some additional data from a database.

When creating the Spreadsheet::Read object, if I manually enter the file name in double quotes, it works fine. When I try to reference the file name from a directory read, it returns a value undefined error and cannot parse the file. I've tried saving $_ to another variable, then injecting double quotes using the escape character into the string, but get the same result.

Is it just not possible to do what I am trying to accomplish, am I doing something wrong, or should I be going about this another way? My relevant code and sample output are below

use strict;
use warnings;
use PerlIO::via::Logger;
use Time::Stamp localstamp => {format => 'compact'};
use DBI;
use Spreadsheet::Read;
use List::MoreUtils qw(uniq);
use String::Util qw(trim);
use Getopt::Long;



my $matchedLog;
my $misMatchedLog;
my $missingLog;
my $timeStamp;
my $tempLog;
my $logging;
my $logFile;
my $targetDir;
my $errored = 0;
my $dbh;
my $sth;
my $sql;
my $book;
my $sheet;
my $cell;
my $callTypeID = 0;
my $initCallType;
my $rnaCallType;
my $xfrCallType;
my $rowCnt = 1;
my @dbRow;
my @callTypeList;
my @fileList;
my @fileDetails;



opendir(DIR, "c:/scripts/modular_collector/collections");

while (readdir (DIR))
{
    # IGNORE NON XLSX FILES
    next if ($_ !~ m/\.xlsx$/);    
    
    $rowCnt = 3;
    print $_ . "\n";
    $book = Spreadsheet::Read->new($_, parser => "xlsx");
    my $numSheets = $book->sheets;
    $numSheets = $numSheets - 1;
    print $numSheets . "\n";
    $sheet = $book->sheet($numSheets);
    
    my $finished = 0;

    while (!$finished)
    {
        $cell = "C" . $rowCnt;
        $callTypeID = $sheet->cell($cell);
        $callTypeID = trim($callTypeID);
    
        if ($callTypeID =~ m/\d+/)
        {
            print "Call Type " . $callTypeID . "\n";
            push(@callTypeList, $callTypeID);
        }
        else
        {
            $finished = 1;
        }
    
        $rowCnt++;
    }
}

closedir(DIR);
@callTypeList = uniq(@callTypeList);
[download]

SAMPLE OUTPUT

-- CODE RUN REFERENCING $_ IN Spreadsheet::Read->new() FAILS --

C:\scripts\call_types>perl call_types.pl
22948850_Config_(10.5).xlsx
XLSX parser cannot parse data: Can't call method "read" on an undefine
+d value at
 call_types.pl line 59.


-- CODE RUN MANUALLY ENTERING FILE NAME ("22948850_Config_(10.5).xlsx"
+) WORKS --
C:\scripts\call_types>perl call_types.pl
22948850_Config_(10.5).xlsx
25
Call Type 5287
Call Type 11376
Call Type 10921
Call Type 5209
Call Type 5210
Call Type 1298
Call Type 1299
Call Type 7358
Call Type 12122
Call Type 12125
Call Type 1303
Call Type 1304
Call Type 12119
Call Type 11373
Call Type 1300
Call Type 13049
Call Type 10143
Call Type 10167
Call Type 10182
Call Type 1428
Call Type 5479
Call Type 5478
Call Type 5480
Call Type 5477
Call Type 5088
Call Type 5089
Call Type 5481
Call Type 5024
Call Type 1426
Call Type 5017
Call Type 5046
Call Type 5028
Call Type 1308
Call Type 5019
Call Type 5047
Call Type 5021
Call Type 1309
Call Type 5130
Call Type 5068
Call Type 5018
Call Type 5131
Call Type 12085
Call Type 1345
Call Type 1346
Call Type 1347
Call Type 1365
Call Type 1369
Call Type 1372
Call Type 1436
Call Type 1348
Call Type 1349
Call Type 1350
Call Type 1351
Call Type 1352
Call Type 1353
Call Type 1429
Call Type 1430
Call Type 1431
Call Type 1368
Call Type 1022
Call Type 5071
[download]