CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Excel Limit Exceeded while writing in Excel Sheet

 



Tejas
Novice

Nov 18, 2013, 11:56 AM

Post #1 of 15 (1103 views)
Excel Limit Exceeded while writing in Excel Sheet Can't Post

Maximum Spreadsheet::WriteExcel filesize, 7087104 bytes, exceeded. To create files bigger than this limit please refer to the "Spreadsheet::WriteExcel::Big" documentation.
at ./Just_Excel.pl line 627

Above is the error the i have encountered today
iTill now i use to take input from a text file and write it excel, probabaly the imput file had less data than today and suddenly above error has occured .Please help me .

Below is th e code

use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Big;
use strict;
use OLE::Storage_Lite;
use Getopt::Std;

my $today = $ARGV[0];
my $pwd = `pwd `;
chomp ($pwd);

my $MX_BR_Source = "$pwd/BRL_MXN_AU_UNBAL_$today";



#******* creation of excel sheet, misc variables needed and their declarations *********
#print "Iteration Done, $counter rows fetched \n Starting Bucketization \n";
our ($aa,$bb,$i, $j, $k, $l, $m, $n, $o, $p, $x,$y) = (1, 1, 1, 1, 1, 1, 1, 1, 1,1,1,1);
our @Values=(0,0,0,0,0,0,0,0);
my $full_path = "$pwd/BRL_MXN_AU_UNBAL_$today.xls";
my $workbook = Spreadsheet::WriteExcel->new($full_path);
my $frmt = $workbook->add_format();
$frmt->set_bold();
$frmt->set_color('blue');
my $date_format = $workbook->add_format(num_format => 'mm/dd/yy');
$date_format->set_num_format('d mmm` yyyy');


#Add Tabs to the worksheet

my $Duplicate_Sale = $workbook->add_worksheet("Duplicate_Sale");
my $Payment_For_Sale_Missing = $workbook->add_worksheet("Payment_For_Sale_Missing");
my $Payment_For_Refund_Missing = $workbook->add_worksheet("Payment_For_Refund_Missing");
my $Refund_Missing = $workbook->add_worksheet("Refund_Missing");
my $Sale_Missing = $workbook->add_worksheet("Sale_Missing");
my $Sale_Vs_Payment_Mismatch = $workbook->add_worksheet("Sale_Vs_Payment_Mismatch");
my $Payment_For_Refund_DblBooking = $workbook->add_worksheet("Payment_For_Refund_DblBooking");
my $Unknown_Bucket = $workbook->add_worksheet("Unknown_Bucket");

I hav ewritten the data to all these tabs till yesterdaybut today due to huge data, nothing works

I came across OLE::Storage_lLite, but really it dint work, i just put the header and i do not have any idea of solving it

Any Help!!
Thanks in Advance


Tejas
Novice

Nov 18, 2013, 8:46 PM

Post #2 of 15 (1082 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

Have i missed writing any important information.

Input file is huge and unable to create an excel format as the input file is huge

In Reply To


FishMonger
Veteran / Moderator

Nov 19, 2013, 7:28 AM

Post #3 of 15 (1073 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

What is your definition of "huge"?

What version of Spreadsheet::WriteExcel are you using?

The Spreadsheet::WriteExcel::Big module is depreciated.

The module's documentaton states:

Quote
The module was a sub-class of Spreadsheet::WriteExcel used for creating Excel files greater than 7MB. However, it is no longer required and is now deprecated.

As of version 2.17 Spreadsheet::WriteExcel can create files larger than 7MB directly if OLE::Storage_Lite is installed.


Based on the error message, it appears that you are using an old version of Spreadsheet::WriteExcel. Your first step should be to upgrade to the latest version and drop the Spreadsheet::WriteExcel::Big module.


(This post was edited by FishMonger on Nov 19, 2013, 7:29 AM)


Laurent_R
Enthusiast / Moderator

Nov 19, 2013, 10:24 AM

Post #4 of 15 (1067 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post


Quote
What is your definition of "huge"?


Same question. For some people, a dozen megabytes is huge. Considering the files I am working on almost daily, a ten gigabyte file does not yet qualify as huge for me.


Tejas
Novice

Nov 20, 2013, 1:53 AM

Post #5 of 15 (1041 views)
Re: [Laurent_R] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

I used the word HUGE because the error said , limit exceeded.
So i thought 7 MB is really big .

The input file i have is around 5 MB and using this file iam intending to writa an excel sheet which exceeded the limit as per the error i have encounter.

Hope u understood my plight behind this word. :)
And I still did not get a workaround to solve this.


Thanks
Tejas


FishMonger
Veteran / Moderator

Nov 20, 2013, 11:20 AM

Post #6 of 15 (1028 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

What about my question on the Spreadsheet::WriteExcel version which you didn't answer?

Did you upgrade to the latest version which is 2.40.

The code you posted doesn't include the section where you're parsing the text file and writing to the spreadsheet. Maybe you're doing something wrong there that is causing the error. It's hard for us to troubleshoot code you haven't shown.


Tejas
Novice

Nov 20, 2013, 11:39 AM

Post #7 of 15 (1024 views)
Re: [FishMonger] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

This actually is in office machine and i dont have permisions for that.
Nevertheless, i have kept the admin posted

And sorry for not posting the full code, as its a bit long(for me :) )
And attached is the full code

Thanks a Lot for your help and i wish to be an expert perl programmer and all ur inputs,suggestions and advices will help me reach this goal.
Also please suggest me the books to read .

Thanks in advance
Tejas
Attachments: new 6.txt (9.96 KB)


Tejas
Novice

Nov 20, 2013, 11:41 AM

Post #8 of 15 (1022 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

please help me if you find the attached code messy

In Reply To
This actually is in office machine and i dont have permisions for that.
Nevertheless, i have kept the admin posted

And sorry for not posting the full code, as its a bit long(for me :) )
And attached is the full code

Thanks a Lot for your help and i wish to be an expert perl programmer and all ur inputs,suggestions and advices will help me reach this goal.
Also please suggest me the books to read .

Thanks in advance
Tejas


Quote


Quote


FishMonger
Veteran / Moderator

Nov 20, 2013, 12:21 PM

Post #9 of 15 (1018 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post


Quote
And attached is the full code


Based on the error message in your opening post, that's about half of the code, probably less. I've only skimmed over it, but there are a lot of problems with that code.

Before doing any modifications to the script, you need to have the admins upgrade the module. Without that upgrade you won't be able to fix the error.


Tejas
Novice

Nov 20, 2013, 8:44 PM

Post #10 of 15 (1008 views)
Re: [FishMonger] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

I would be glad if u can tell me the problem with the code,
I would like to improve it basedon your suggestions.
Upgrade might take some time ,so iam planning to break the input file to two and run it.

Please tell me what improvememnt can i make to this code

Thanks
Tejas


FishMonger
Veteran / Moderator

Nov 20, 2013, 9:12 PM

Post #11 of 15 (1006 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

Start by running your code through perlcritic - Critique Perl source code for best-practices.
http://search.cpan.org/~thaljef/Perl-Critic-1.121/lib/Perl/Critic.pm

You may need to install that module either on the server or your local computer. Once it's installed, it provides you with a "perlcritic" command line tool that checks your script and tells you about the problems.

If you post the complete script as an attachment, I can run it through perlcritic for you.

If they're not already in the script, you should add these 2 lines (pragmas) near the beginning which should be in EVERY perl script you write.

Code
use strict; 
use warnings;



Tejas
Novice

Nov 20, 2013, 10:20 PM

Post #12 of 15 (1004 views)
Re: [FishMonger] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

I have posted the complete script as an attachment , only i have not provided the input file.

I will be glad if u run it !!

Thanks
Tejas


FishMonger
Veteran / Moderator

Nov 21, 2013, 6:23 AM

Post #13 of 15 (988 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post


In Reply To
I have posted the complete script as an attachment , only i have not provided the input file.


Really?

The error message points to line 627, but the code you attached only has 338 lines and does not even include the use statements that your opening post showed.. I would not call that complete.

Here's an incomplete list of issues.

Quote
c:\test>perl -Mstrict -cw Just_Excel.pl
Scalar value @row[3] better written as $row[3] at Just_Excel.pl line 59.
Scalar value @month[$i] better written as $month[$i] at Just_Excel.pl line 59.
Scalar value @row[3] better written as $row[3] at Just_Excel.pl line 61.
Scalar value @year[0] better written as $year[0] at Just_Excel.pl line 61.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 120.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 121.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 122.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 123.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 124.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 131.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 134.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 135.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 135.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 145.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 146.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 147.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 148.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 149.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 156.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 160.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 161.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 161.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 173.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 174.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 175.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 176.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 177.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 184.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 188.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 189.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 189.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 203.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 204.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 205.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 206.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 207.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 214.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 219.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 220.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 220.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 234.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 235.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 236.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 237.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 238.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 246.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 251.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 252.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 252.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 267.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 268.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 269.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 270.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 271.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 280.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 284.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 285.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 285.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 296.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 297.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 298.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 299.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 300.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 306.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 309.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 310.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 310.
Scalar value @_[5] better written as $_[5] at Just_Excel.pl line 324.
Scalar value @_[6] better written as $_[6] at Just_Excel.pl line 325.
Scalar value @_[7] better written as $_[7] at Just_Excel.pl line 326.
Scalar value @_[8] better written as $_[8] at Just_Excel.pl line 327.
Scalar value @_[9] better written as $_[9] at Just_Excel.pl line 328.
Scalar value @_[$ii] better written as $_[$ii] at Just_Excel.pl line 331.
Scalar value @_[3] better written as $_[3] at Just_Excel.pl line 335.
Scalar value @_[11] better written as $_[11] at Just_Excel.pl line 336.
Scalar value @_[2] better written as $_[2] at Just_Excel.pl line 336.
Global symbol "$pwd" requires explicit package name at Just_Excel.pl line 3.
Global symbol "$today" requires explicit package name at Just_Excel.pl line 3.
Global symbol "$MX_BR_Source" requires explicit package name at Just_Excel.pl line 44.
syntax error at Just_Excel.pl line 191, near "else"
Missing right curly or square bracket at Just_Excel.pl line 338, at end of line
syntax error at Just_Excel.pl line 338, at EOF
Just_Excel.pl had compilation errors.



Tejas
Novice

Nov 24, 2013, 10:53 AM

Post #14 of 15 (917 views)
Re: [FishMonger] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

Uploaded my file.
Please check it and please suggest if anythong has to be changed.
Now, i have uploaded the full file
Deletd unncessary lines of code too

Thank You
Attachments: JustExcel.pl (11.0 KB)


Laurent_R
Enthusiast / Moderator

Nov 24, 2013, 1:38 PM

Post #15 of 15 (886 views)
Re: [Tejas] Excel Limit Exceeded while writing in Excel Sheet [In reply to] Can't Post

If you want to access the content of the second element of the array @array, the right syntax is: $array[1], not @array[1].

Especially, all your subroutines should have "my $foo = $_[5];", not "my $foo = @_[5];"

Correcting this should eliminate dozens of warnings when compiling your program. Once those warnings are gone, you might concentrate on the other more important ones. And believe me, don't even consider looking at the outcome of your program so long as you have such warnings, because they tell you important mistakes about your program (even if it seems to produce the right output).

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives