Read Excel file via Perl

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

I have the the following code with code which work in window 10 but isn't work in Linux.

Error I get is as following:

Attempt to reload Spreadsheet/ParseXLSX.pm aborted.

Compilation failed in require at ./CreInventoryFromExcel.pl line 35.

BEGIN failed--compilation aborted at ./CreInventoryFromExcel.pl line 35.

I have the following modules under the PWD:

ls -R lib/Spreadsheet/

lib/Spreadsheet/:

ParseExcel ParseExcel.pm ParseXLSX ParseXLSX.pm Read.pm

lib/Spreadsheet/ParseExcel:

Cell.pm Dump.pm FmtDefault.pm FmtJapan2.pm FmtJapan.pm FmtUnicode.pm Font.pm Format.pm SaveParser SaveParser.pm Utility.pm Workbook.pm Worksheet.pm

lib/Spreadsheet/ParseExcel/SaveParser:

Workbook.pm Worksheet.pm

lib/Spreadsheet/ParseXLSX:

Decryptor Decryptor.pm

lib/Spreadsheet/ParseXLSX/Decryptor:

Agile.pm Standard.pm

uname -a

Linux eaasrt 3.10.0-514.el7.x86_64 #1 SMP Wed Oct 19 11:24:13 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

#!/usr/bin/perl -w

BEGIN
{
    push(@INC, $ENV{'PWD'}."/lib");
    push(@INC, $ENV{'PWD'}."/lib/Spreadsheet");
}

use strict;
use warnings;
use Getopt::Std;
use Time::Local;
use File::Basename;
use Switch;
use Data::Dumper qw(Dumper);

...
$i_excelSheetName = $optionsHash{'s'} ;
...

my $book = ReadData ($i_excelFileName);

my @lines ;
my $inLineTmp;
my $sheetID = $book->[0]{'sheet'}{$i_excelSheetName};
if (! defined $sheetID) {
    print "\n\nError: Not found sheen name ".$i_excelSheetName." in In
+put Excel file.\n";
    print "Available Sheets in Excel are: \n";
    print Dumper \$book->[0]{'sheet'};
    die "Program stopped.\n\n";
}
my @rows = Spreadsheet::Read::rows($book->[$sheetID]);

# Go over all lines. 'scalar' return the total number of lines. Since 
+we should start from line 0, loop initial to 1 and we used i-1
foreach my $i (1 .. scalar @rows) {
    $inLineTmp = $rows[$i-1][0];
    foreach my $j (1 .. scalar @{$rows[$i-1]} -1 ) {
        if (defined ($rows[$i-1][$j] )) {
            $inLineTmp .= ",".$rows[$i-1][$j];
        } else {
            $inLineTmp .= ",";
        }
        
    }
    push @lines , $inLineTmp;
}
[download]

Comment on Read Excel file via Perl Download Code

Replies are listed 'Best First'.
Re: Read Excel file via Perl by alexander_lunev (Pilgrim) on Sep 17, 2018 at 17:50 UTC
Hello and welcome! How do you run your Perl program on Windows? There are several Perl ports to Windows (ActiveState, Strawberry), and some of them already have some non-core modules preinstalled as part of distribution. While on Unix systems Perl distribution contains only base (or core) modules of Perl itself. So the error is quite explanatory: some of dependencies couldn't be loaded, and it seems that module SpreadSheet::ParseXLSX have quite a number of them. And if on Windows your program works, and on Linux it doesn't, then it's quite possible that your Windows Perl distribution have those dependencies while Linux Perl distribution have not. Don't use modules by unpacking them into 'lib' dir. You should install modules with package manager of your OS instead, or with Perl CPAN module using its 'shell' function like this (from command prompt on Linux): `> sudo perl -MCPAN -eshell` and then in CPAN shell prompt: `> install SpreadSheet::ParseXLSX`	[reply] [d/l] [select]
Re: Read Excel file via Perl by thanos1983 (Parson) on Sep 17, 2018 at 17:40 UTC
Hello AE1602, Welcome to the Monastery. Can you show us your code that the script is failing? From the error `BEGIN failed--compilation aborted at ./CreInventoryFromExcel.pl line 35.` is complaining for line 35. On your sample of code the Begging is on line 3. Update: See sample of code bellow this assumption is not correct. From a quick view are you sure that `push(@INC, $ENV{'PWD'}."/lib");` runs the same on WindowsOS and LinuxOS? Do you mean `pwd` on LinuxOS maybe? Sample: `$ PWD PWD: command not found $ pwd /home/user` [download] `#!/usr/bin/perl use strict; use warnings; print "\$ENV{PWD} is:\t" . $ENV{PWD} . "\n"; __END__ $ perl test.pl $ENV{PWD} is: /home/user` [download] Also you do need to use `#!/usr/bin/perl -w` the `-w` on your shebang while you use `use warnings`. I assume that your code is running on WindowsOS but not on LinuxOS. Taken in consideration this assumption be aware that 6 Ways the Linux File System is Different From the Windows File System LinuxOS is case sensitive and WindowsOS is not. Double check the file names and please prepare a small sample of executable script that demonstrates the problem. We can not replicate your problem based on the given information. I would recommend prepare a small dir that you import and from there load one module that simply reads the excel file. If this works then the rest should work. Looking forward to your update. BR / Thanos Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^2: Read Excel file via Perl by alexander_lunev (Pilgrim) on Sep 17, 2018 at 18:00 UTC
PWD in `$ENV{'PWD'}` is not a `pwd` command, but an environment variable in %ENV hash, which contains current dir on Windows and on *nix systems. So the line `push(@INC, $ENV{'PWD'}."/lib");` will correctly add current dir to @INC array in both worlds.	[reply] [d/l] [select]
Re^3: Read Excel file via Perl by thanos1983 (Parson) on Sep 17, 2018 at 18:28 UTC
Hello alexander_lunev, You are right I did not know that this works also on LinuxOS. Sample: `#!/usr/bin/perl use strict; use warnings; print "\$ENV{PWD} is:\t" . $ENV{PWD} . "\n";` [download] Thanks for correcting me. BR Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re: Read Excel file via Perl by poj (Abbot) on Sep 17, 2018 at 17:53 UTC
How did you install the `Spreadsheet::Read` module on the Linux machine ? poj	[reply] [d/l]