How does it fail?

The old Excel format as produced by Spreadsheet::WriteExcel can only contain 65535 lines per sheet. Maybe that is your problem?

yes it should support but here it is not, I had to kill the script every time, it was running for hours and returning nothing.

G'day raja567,

Welcome to the monastery.

"I had to kill the script every time, it was running for hours and returning nothing."

You're reading your input from your output file:

   open(INPUT,$outFile) or die "Can't open file";
[download]

You haven't shown enough of your code but I suspect you have something like this (before the code you have shown):

    my $workbook = Spreadsheet::WriteExcel->new($outFile);
    my $worksheet = $workbook->add_worksheet();
[download]

Your code keeps reading what you've just written. You're effectively in an infinite loop.

I'd also recommend you look at open for a better way to open your files (using 3-argument form with a lexical filehandle) and write the die "message" (including the actual filename and $!).

You also appear to have an error with this:

$worksheet->write(0,$colCount,$el,$gFmtBold)   ;
[download]

That's writing everything to row 0! I suspect this is what you want:

        $worksheet->write($rowCount, $colCount, $el, $gFmtBold);
[download]

Not an error, but on every iteration of the foreach loop you're setting the width for every column that you've already set:

                          $worksheet->set_column(0,$colCount,40);
[download]

That's possibly a lot of additional processing you don't need. Given the amount of data you're dealing with, that could well be noticeable timewise. Would this not be better:

        $worksheet->set_column($colCount, $colCount, 40);
[download]

The layout of your code is far from optimal. If you employ a logical scheme of indentation, the logic of your code will be far more apparent. This will help you and anyone else who has to subsequently read it. The "perlstyle - Perl style guide" provides tips on how to do this; you can automate the process with perltidy.

[Disclaimer: I'm not a user of Spreadsheet::WriteExcel. The above information was gleaned by reading the documentation.]

-- Ken

I guess it looks like buffer issue, do you have $|=1; at the begin of the script? if not add and try again.

---

All is well

Your posted code doesn't indicate that you're doing any manipulation of the data. Is that correct, or did you leave out that portion of your code?

Excel knows how to import csv (or pipe delimited) files. Have you tried opening the file directly in excel? If so, will it load properly?

How big is the file? If the resulting excel file is over 7MB, Spreadsheet::WriteExcel will require the use of the OLE::Storage_Lite module. Do you have that module installed?

Looks like you want to convert pipe-separated data to Excel. Would csv2xls be an option for you?

$ csv2xls -s'|' -o file.xls file.txt
[download]

This utility does essentially what you describe, but switches to using Spreadsheet::WriteExcel::Big if the size of the input file is over 5Mb

Maybe just changing use Spreadsheet::WriteExcel; to use Spreadsheet::WriteExcel::Big; will fix your issue.

update: it used to switch to the ::Big version until Oct 2011, when I noted that Spreadsheet::WriteExcel deprecated ::Big back in 2007.

Spreadsheet::WriteExcel::Big is depreciated.

DESCRIPTION

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.

This module only exists for backwards compatibility. If your programs use ::Big you should convert them to use Spreadsheet::WritExcel directly.

I know, but the OP did not state a version being used, so the ::Big version */might/* be a solution for him.


Enjoy, Have FUN! H.Merijn