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

Hi all,
I was wondering if there was any limitation on the number of files that the WriteExcel module can create. My situation is this:

I am creating about 184 excel files from text files. No matter what sequence i output these files at (ex: 1 to 81 or 50 to 131), whenever it reaches the 81st output file, I get the following error: 
Can't locate Carp/Heavy.pm in @INC (@INC contains: C:/Perl/lib C:/Perl
+/site/lib
.) at C:/Perl/lib/Carp.pm line 109, <inFile> line 1.
        (in cleanup) Can't use an undefined value as a HASH reference 
+at C:/Perl
/site/lib/Spreadsheet/WriteExcel/Workbook.pm line 480.

[download]
I can't figure out what is going on, since it always happens on the 81st file but the exact file its writing at the time can change depending on the sequence. I can still accomplish what i want by running the script 3 separate times over different sequences but I'd like to know what could be causing this.

-Toan

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel # of files limitation?
by jmcnamara (Monsignor) on Jul 16, 2002 at 21:43 UTC

    I can't replicate this bug. I tried the following program on Win98 with perl5.005 and on Linux with a variety of perls. In all cases it produced 100 files. Could you try it on your system? 
        #!/usr/bin/perl -wl

    use strict;
    use Spreadsheet::WriteExcel;

    my $filename = 'test001';

    $|++;

    for (1..100) {

        my $workbook  = Spreadsheet::WriteExcel->new($filename . '.xls
+');
        my $worksheet = $workbook->addworksheet();

        $worksheet->write(0, 0,  "Hi Excel!");
        $workbook->close();

        print "Writing file : ", $filename++;
    }

    [download]

    Perhaps you could send me a more detailed bug report with your OS, version of perl, version of Spreadsheet::WriteExcel and the program that is causing the problems (or the smallest possible program that demonstrates this behaviour).

    --
    John.

[reply]
[d/l]
      John,

      I am running win2k, perl 5.6.1.633, and version 0.37 of SS:WE. I tried just changing the max number of files from 100 to 1000 and encountered the same error after file number 502. I am writing to a networked drive on a unix machine. I also tried this on an NT machine, writing to a local drive (just to check whether this is a windows limitation) and encountered the same error as well after file number 502. My script was crashing on file 79 because I had more worksheets I think. So adding more worksheet will reduce when it errors. I also checked the total size of the xls files and it is not close to the 7mb limit I heard about.

      Please let me know if you encounter the same error

      Thanks,
      Toan

      PS: Great job writing this module by the way, a great tool.
[reply]

        This is a bug.

        I tracked it down to a feature of File::Temp and the way that temp files are handled on Windows.

        It will be patched in version 0.38 of Spreadsheet::WriteExcel which is due for release in the next 2-3 weeks.

        If you need the modification before that I can send you a patch or you can roll back to version 0.36.

        Thanks for highlighting this.

        --
        John.

[reply]
Re: Spreadsheet::WriteExcel # of files limitation?
by RMGir (Prior) on Jul 16, 2002 at 17:39 UTC
    Do you call $workbook->close() when you finish each workbook?

    Are you also closing your input files?

    Most OS's limit how many files a process can have open at once, so if you're not closing the files, you're going to run out.

    If you're doing all those things, then there could be a bug in SS::WE...
    --
    Mike

[reply]
      Yea, i'm closing the input files as soon as i'm done reading from them and I'm closing the output files with $workbook->close() as well.
[reply]
        (Edit: Got rid of non-working code that tried to check lsof output)

        I guess this experiment didn't prove much...

        But looking at the code in the WriteExcel module, things seem to get closed when they should, so I'm puzzled...
        --
        Mike

[reply]

Back to Seekers of Perl Wisdom