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
SAMPLE OUTPUTuse 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);
-- 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
In reply to Spreadsheet::Read Open File from Variable? by CJ2012SXT
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |