[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

Out of memory error while parsing a specific excel file

When the perl code is run the program generates some warnings and terminates. The output is as below:


D:\Perl\bin\search tool>perl testa.pl
UTF-16 surrogate 0xdb79 at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
UTF-16 surrogate 0xdbb1 at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
UTF-16 surrogate 0xd83e at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
UTF-16 surrogate 0xdff8 at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
UTF-16 surrogate 0xdbff at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
UTF-16 surrogate 0xdd98 at D:/Perl/site/lib/Spreadsheet/ParseExcel/
FmtDefault.pm line 81.
substr outside of string at D:/Perl/site/lib/Spreadsheet/ParseExcel.pm
+
line 1196.
Use of uninitialized value in unpack at D:/Perl/site/lib/Spreadsheet/
ParseExcel.pm line 1196.
substr outside of string at D:/Perl/site/lib/Spreadsheet/ParseExcel.pm
+
line 1196.
Use of uninitialized value in unpack at D:/Perl/site/lib/Spreadsheet/
ParseExcel.pm line 1196.
Out of memory!



The system information is as below :

Perl version : 5.010000
OS name : MSWin32
Module versions: (not all are required)
Spreadsheet::ParseExcel 0.49
Scalar::Util 1.19
Unicode::Map (not installed)
Spreadsheet::WriteExcel (not installed)
Parse::RecDescent (not installed)
File::Temp 0.18
OLE::Storage_Lite 0.18
IO::Stringy 2.110

The perl code is as below and the sample file is attached.
use strict; 
    use Spreadsheet::ParseExcel; 
    my $parser = Spreadsheet::ParseExcel->new( 
        CellHandler => \&cell_handler, 
        NotSetCell  => 1 
    ); 
    my $workbook = $parser->Parse('bad.xls'); 
    sub cell_handler { 
        my $workbook    = $_[0]; 
        my $sheet_index = $_[1]; 
        my $row         = $_[2]; 
        my $col         = $_[3]; 
        my $cell        = $_[4]; 
        print $cell->unformatted(), "\n"; 
    }

Open in new window

bad.xls
0
triharish
Asked:
triharish
  • 15
  • 7
  • 2
1 Solution
 
TobiasCommented:
Dear Triharish,

I have try with the file and I get the same error.

I have try to correct the module but too many error become after that I correct the line 1196.

I suggest you to export the file as csv and user another module to open the file as csv like Text::CSV_XS.

Best Regards
0
 
triharishAuthor Commented:
MadShiva


There are a large number of excel files.

The overhead will increase if the program converts each of those excel files and then opens them.

What do you think ?
0
 
TobiasCommented:
Dear Triharish,

I have found the solution, it's quite simple... but hard to find :)

Unlock the worksheet and everything should work.

Best Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
triharishAuthor Commented:
Dear MadShiva

Thats not possible.

The program traverses through multiple excel files and it is supposed to be an automated tool.
0
 
TobiasCommented:
Dear

Ok but the Spreadsheet::ParseExcel  module don't support one excel file that is protected.

Also the protection of the excel file is not one protection because you could remove it, or any user can remove it.

Maybe the solution for you is to unprotected the file with perl but I don't know how to make this, and after that you have get the content of the file you protected again the file.

Best Regards

0
 
triharishAuthor Commented:
Dear


To unprotect an excel file you need to know the password of that file.


How can I know the password of a protected excel file which is present on a client users machine?

OR

What if I dont know the password of a protected excel file ?
0
 
triharishAuthor Commented:
OR

Is there any way to detect protected excel files so that I can skip them ?
0
 
TobiasCommented:
Dear,

To unprotect an excel file you need to know the password of that file.

No, with one macro you can remove the protection of the excel file. (It's what I have do with your file for that the script perl work and see where is the fault on the script.

Url for the macro that remove the protection : http://www.straxx.com/excel/password.html


For unprotect the file see the attached code but with this method you need to know the password.


Best Regards


use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
 $Win32::OLE::Warn = 3;
 
 $| = 1;  
 
   
 my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
 || Win32::OLE->new('Excel.Application', 'Quit');
 
 $Excel->{Visible} = 1;
 my $Book = $Excel->Workbooks->Open("file.xls");
 my $Sheet = $Book->Worksheets(2);
 
 
 $Book->UnprotectSharing("password");  # It works very good 
 $Book->Save;    
 $Book->Close;

Open in new window

0
 
triharishAuthor Commented:
Dear

No manual method will work as there are a large number of protected and unprotected excel files.

Also I don't know the password.
0
 
triharishAuthor Commented:
Is there a way to programmatically detect the excel files whose sheets are protected ?
0
 
TobiasCommented:
Dear,

I have made this, but is not perfect.

Best Regards
#! C:/Perl/bin/perl -w
 
use Win32::OLE;
use File::Copy;
use Cwd;
 
use Win32::OLE::Const 'Microsoft Excel';
#$Win32::OLE::Warn = 2;        # die on errors...
 
# Start Excel and make it invisible
      my $xlApp = Win32::OLE->new('Excel.Application');
      $xlApp->{Visible} = 0;
 
      # Open excel file.
      my $xlBook = $xlApp->Workbooks->Open("C:\\Documents and Settings\\username\\Desktop\\bad.xls");
 
       my $Sheet1 = $xlBook ->Worksheets(1);
 
	my $row = 1;
	my $col = 1;
 
       $Sheet1->Cells($row,$col)->{'Value'} = 5;
 
	if (Win32::OLE->LastError) {
  	print "File protected";
	}
 
      $xlBook ->SaveAs("C:\\Documents and Settings\\username\\Desktop\\temp.xls");
      $xlBook ->Close();
 
 undef $xlBook;

Open in new window

0
 
Adam314Commented:
Depending on what information you need from the excel file, you might be able to use xls2csv.
    http://vitus.wagner.pp.ru/software/catdoc/

This will (should) work if you only need the data info, but not the formatting info.  Once you have the csv data, you can use perl to process it.
0
 
triharishAuthor Commented:
Dear

When I run the code on a normal and another protected excel file the below error occurs :

Can't call method "Worksheets" on an undefined value at testa.pl line 17.

I have attached the files.
continuous.xls
sam.xls
0
 
triharishAuthor Commented:
Dear MadShiva

I think I didnt give the full path of the file.

So please ignore the above message.
0
 
triharishAuthor Commented:
Dear Adam314


If there are 100 excel files then don't you think that converting each one of them to CSV will slow down the program  ?
0
 
triharishAuthor Commented:
Dear MadShiva

The code is fine.


Its prompting a message to "save the file or not".



How to supress this message ?
0
 
triharishAuthor Commented:
The following code will work :
use Win32::OLE;
 
# Start Excel and make it invisible
      my $xlApp = Win32::OLE->new('Excel.Application');
      
      $xlApp->{DisplayAlerts} = 0;
       
      # Open excel file.
      my $xlBook = $xlApp->Workbooks->Open("C:\\Documents and Settings\\username\\Desktop\\bad.xls");
      my $Sheet1 = $xlBook->Worksheets(1);
       
        my $row = 1;
        my $col = 1;
 
       $Sheet1->Cells($row,$col)->{'Value'} = 5;
 
        if (Win32::OLE->LastError) 
        {
        	print "File protected";
        }
 
      $xlBook ->Close();
 
  undef $xlBook;

Open in new window

0
 
triharishAuthor Commented:
The attached file is opening even if  $xlApp->{Visible} = 0;


What to do ?
test.xls
0
 
TobiasCommented:
Dear triharish,

Yes, i'm trying to find how to set the password before open the file.

If we can set the password ( a bad password) the code will work.

I have make this perl module with your code.



Best Regards
package ExcelFileIsProtected;
 
sub ExcelFileIsProtected
{
my ($path_to_file) = @_;       # One argument wait
use Win32::OLE;
 
#  
# Start Excel and make it invisible
      my $xlApp = Win32::OLE->new('Excel.Application');
      
      $xlApp->{DisplayAlerts} = 0;
       
      # Open excel file.
      my $xlBook = $xlApp->Workbooks->Open($path_to_file);
      my $Sheet1 = $xlBook->Worksheets(1);
       
        my $row = 1;
        my $col = 1;
 
       $Sheet1->Cells($row,$col)->{'Value'} = 5;
 
        if (Win32::OLE->LastError) 
        {
                return true;
        }
	else	
	{
		return false;
	}
 
 
 $xlBook ->Close();
 
  undef $xlBook;
}
 
 
 
1;

Open in new window

0
 
triharishAuthor Commented:
Dear


Are you going to upload  this package in CPAN ?
0
 
triharishAuthor Commented:
Can I upload the package in CPAN ?
0
 
triharishAuthor Commented:
Please look into the last problem also.
0
 
Adam314Commented:
>>If there are 100 excel files then don't you think that converting each one of them to CSV will slow down the program

Not necessarily.  It might even be faster.  It could be slower though - I haven't tried it.
0
 
TobiasCommented:
Dear triharish,

I don't have find one solution with the perl code, but like Adam314 says,
if you only want retrieve data from the excel file you could use xls2csv,
this utility warn you that the file is encrypted and don't ask for a password.

Also I have read the source of the xlsparser.c that come with xls2csv,
but I don't understand how they get that the file is encrypted.

You could also use xls2csv to know if the file is encrypted but for me it's not a good method (too slow) but they work.

Then the best way, and the fastest is to use xls2csv to convert each excel file.

Yes you could upload the package in CPAN, but it's not really finished and have a bug ;)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 15
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now