topic PERL .csv file new sheet in Operating System - Linux

PERL .csv file new sheet

Anand_30 — Fri, 29 Dec 2006 13:33:56 GMT

Hi,

I have written a PERL script to create a .csv file and write some data in it. Now I want to use a different file handle and create a new worksheet in the same .csv file and write data in it.

Can anyone please let me know how to accomplish the task.

Thanks,
Anand

Re: PERL .csv file new sheet

H.Merijn Brand (procura — Fri, 29 Dec 2006 13:59:30 GMT

You can't. CSV does not support multiple sheets.

Enjoy, Have FUN! H.Merijn

Re: PERL .csv file new sheet

spex — Fri, 29 Dec 2006 14:22:27 GMT

Hi Anand,

You'll need the Spreadsheet::WriteExcel perl module.

my $workbook = Spreadsheet::WriteExcel->new('filename.xls');
my $worksheet = $workbook->add_worksheet();
$worksheet->write(0, 0, "Hi Excel!");

http://search.cpan.org/dist/Spreadsheet-WriteExcel/

PCS

Re: PERL .csv file new sheet

Hein van den Heuvel — Fri, 29 Dec 2006 16:05:38 GMT

Anand>> create a new worksheet in the same .csv file

Merijn>> You can't. CSV does not support multiple sheets.

Right, but I don't like the wording.

You can do anything you darn well please in a .csv file.
It supports anything you can imagine and code up.
It could easily support a notion that a row/line/record with starts a section corresponding to a worksheet to be terminated by:

However, do not expect EXCEL to interpret the data thusly.

Microsoft EXCEL only supports a single worksheet in a csv file.
If you want more, you'll have to code to a format for which excel support worksheets.

Anand,
May we assume that you want to feed the csv file to excel? Then use the perl Spreadsheet::WriteExcel moduel as Spex indicate, or figure out how to write XML instead of CSV.
Hint: Go into EXCEL, create a simple spreadsheet with two or three worksheets, a few columns and data lines each, no formatting or formulas (to begin with) and SAVE AS ... .XML.
No open the result with NOTEPAD.

Hope this helps,
Hein.

Re: PERL .csv file new sheet

H.Merijn Brand (procura — Fri, 29 Dec 2006 16:32:20 GMT

Hein, nice(er) words, but that will violate the (very simple) csv specs.

CSV does not support multiple sheets. period.

Any other format M$Excel, OpenOffice Calc, Improv, Oleo, XML, ... does, but csv does not

For all the others there are indeed fine modules, well, maybe not for oleo, but still. And CSV is not, and does not support, (embedded) XML.

Write two files: sheet1.csv, and sheet2.csv: see: two sheets, both obeing the specs (if you've used COMMA's and not M$'s semi-colons, and correct quoting, which is easily achieved using Text::CSV_XS or Text::CSV.

BTW *REAL* CSV has \r\n on the line ending, something violated quite often too.

Enjoy, Have FUN! H.Merijn [ A heavy CSV user ]