and EZ way to convert XLS file to CSV?

[Sara]

OK, I'm painted into a corner by Bill G again.. This time with XL, yet
another package NIH by Microsoft but still controlled by them...

If I bop into OpenOffice I can read in an XLS and export it as a CSV.
I looked in cpan and news but didn't see a straightforward way to do
the same thing with a Perl module. I saw XL-Parse but the readme
implied it didn't have a quick-conversion capability.

Something, perhaps, like

my @csv = DreamXLModule::Convert($XLFilePath, 'csv');

Thanks!
Gx

[Garry Short]

Sara wrote:

> OK, I'm painted into a corner by Bill G again.. This time with XL, yet
> another package NIH by Microsoft but still controlled by them...
>
> If I bop into OpenOffice I can read in an XLS and export it as a CSV.
> I looked in cpan and news but didn't see a straightforward way to do
> the same thing with a Perl module. I saw XL-Parse but the readme
> implied it didn't have a quick-conversion capability.
>
> Something, perhaps, like
>
> my @csv = DreamXLModule::Convert($XLFilePath, 'csv');
>
> Thanks!
> Gx

Not seen one, sorry. You could write your own without too much trouble,
using Spreadsheet::ParseExcel, something like this:

--

#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
my ($file) = (@ARGV);
my $dest = $file;
my $xls = Spreadsheet::ParseExcel::Workbook->Parse("$file");
foreach my $sheet (@{$xls->{Worksheet}}) {
my $name = $sheet->{Name};
my $dest =~ s/\.xls$/_sheet$name\.cvs/;
open OUT, ">$dest" or die "Can't write to $dest: $!\n";
my ($col, $row);
for ($row = $sheet->{MinRow};
defined $sheet->{MaxRow} && $row <= $sheet->{MaxRow};
$row++) {
for ($col = $sheet->{MinCol};
defined $sheet->{MaxCol} && $col <= $sheet->{MaxCol};
$col++){
my $cell = $sheet->{Cells}[$row][$col]->Value;
print OUT "$cell,";
}
print OUT "\n";
}
close OUT;
}

--

It should write every sheet to a different file, in the form
<filename>_<sheetname>.cvs
Not tested, but it may work. Gives you the idea, anyway.

Two things, though - this module throws up lots of warnings, so you may wish
to switch them off once you get it working.
Also, this won't be quick (from recent experience, I'd guess about 15mins
for an xls with 12 sheets, each around 150x400. That's on a P3/700mhz with
128mb RAM & Linux, just to give you an idea)!

Hopefully this'll give you a good start on writing it, anyway. It may just
need a check to make sure the cell's not empty.

Regards,

Garry

[Helgi Briem]

On 10 Jul 2003 06:25:45 -0700, [email]genericax@hotmail.com[/email] (Sara) wrote:

>OK, I'm painted into a corner by Bill G again.. This time with XL, yet
>another package NIH by Microsoft but still controlled by them...
>
>If I bop into OpenOffice I can read in an XLS and export it as a CSV.
>I looked in cpan and news but didn't see a straightforward way to do
>the same thing with a Perl module. I saw XL-Parse but the readme
>implied it didn't have a quick-conversion capability.

Are you talking about Excel, by any chance?

use Spreadsheet::ParseExcel;

[A. Sinan Unur]

[email]genericax@hotmail.com[/email] (Sara) wrote in news:776e0325.0307100525.37b06a42
@posting.google.com:

> OK, I'm painted into a corner by Bill G again.. This time with XL, yet

I do not know what XL is but I'll assume you are talking about Excel.

> another package NIH by Microsoft but still controlled by them...

NIH?

> If I bop into OpenOffice I can read in an XLS and export it as a CSV.
> I looked in cpan and news but didn't see a straightforward way to do
> the same thing with a Perl module. I saw XL-Parse but the readme
> implied it didn't have a quick-conversion capability.

I do not know about modules, but some time ago, I wrote a very
rudimentary method to save each sheet in a bunch of Excel files in text
format. If you are on a Win32 system with Excel installed, you could use
that method. See:

[url]http://www.people.cornell.edu/pages/asu1/notes/perl-excel.html[/url]

Sinan
--
A. Sinan Unur
[email]asu1@c-o-r-n-e-l-l.edu[/email]
Remove dashes for address
Spam bait: mailto:uce@ftc.gov

[Sara]

"A. Sinan Unur" <asu1@c-o-r-n-e-l-l.edu> wrote in message news:<Xns93B49CBD9408Easu1cornelledu@132.236.56.8> ...

> [email]genericax@hotmail.com[/email] (Sara) wrote in news:776e0325.0307100525.37b06a42
> @posting.google.com:
>

> > OK, I'm painted into a corner by Bill G again.. This time with XL, yet

>
> I do not know what XL is but I'll assume you are talking about Excel.

Yes sorry I'm not fluent in Microsoft-ese...

>

> > another package NIH by Microsoft but still controlled by them...

>
> NIH?

Not Invented Here
_ _ _

>

> > If I bop into OpenOffice I can read in an XLS and export it as a CSV.
> > I looked in cpan and news but didn't see a straightforward way to do
> > the same thing with a Perl module. I saw XL-Parse but the readme
> > implied it didn't have a quick-conversion capability.

>
> I do not know about modules, but some time ago, I wrote a very
> rudimentary method to save each sheet in a bunch of Excel files in text
> format. If you are on a Win32 system with Excel installed, you could use
> that method. See:
>
> [url]http://www.people.cornell.edu/pages/asu1/notes/perl-excel.html[/url]

Hmm I see- and I appreciate the tip and offer. Luckily for me I'm not
on a Billy-G platform, I have either Solaris or Linux at my diposal.

Regards, and have a nice weekend!
Gx

>
> Sinan

[Sara]

Garry Short <g4rry_short@zw4llet.com> wrote in message news:<bejstc$msb$1$8302bc10@news.demon.co.uk>...

> Sara wrote:
>

> > OK, I'm painted into a corner by Bill G again.. This time with XL, yet
> > another package NIH by Microsoft but still controlled by them...
> >
> > If I bop into OpenOffice I can read in an XLS and export it as a CSV.
> > I looked in cpan and news but didn't see a straightforward way to do
> > the same thing with a Perl module. I saw XL-Parse but the readme
> > implied it didn't have a quick-conversion capability.
> >
> > Something, perhaps, like
> >
> > my @csv = DreamXLModule::Convert($XLFilePath, 'csv');
> >
> > Thanks!
> > Gx

>
> Not seen one, sorry. You could write your own without too much trouble,
> using Spreadsheet::ParseExcel, something like this:
>
> --
>
> #!/usr/bin/perl -w
> use strict;
> use Spreadsheet::ParseExcel;
> my ($file) = (@ARGV);
> my $dest = $file;
> my $xls = Spreadsheet::ParseExcel::Workbook->Parse("$file");
> foreach my $sheet (@{$xls->{Worksheet}}) {
> my $name = $sheet->{Name};
> my $dest =~ s/\.xls$/_sheet$name\.cvs/;
> open OUT, ">$dest" or die "Can't write to $dest: $!\n";
> my ($col, $row);
> for ($row = $sheet->{MinRow};
> defined $sheet->{MaxRow} && $row <= $sheet->{MaxRow};
> $row++) {
> for ($col = $sheet->{MinCol};
> defined $sheet->{MaxCol} && $col <= $sheet->{MaxCol};
> $col++){
> my $cell = $sheet->{Cells}[$row][$col]->Value;
> print OUT "$cell,";
> }
> print OUT "\n";
> }
> close OUT;
> }
>
> --
>
> It should write every sheet to a different file, in the form
> <filename>_<sheetname>.cvs
> Not tested, but it may work. Gives you the idea, anyway.
>
> Two things, though - this module throws up lots of warnings, so you may wish
> to switch them off once you get it working.
> Also, this won't be quick (from recent experience, I'd guess about 15mins
> for an xls with 12 sheets, each around 150x400. That's on a P3/700mhz with
> 128mb RAM & Linux, just to give you an idea)!
>
> Hopefully this'll give you a good start on writing it, anyway. It may just
> need a check to make sure the cell's not empty.
>
> Regards,
>
> Garry

Garry:

MOST APPRECIATED dude- you didn't have to go to all of that trouble! I
figured with this probably being a very common conversion, that there
would be a ton of modules out there. But your solution should work
fine- I'm gonna give it a go here shortly once I get the module
insalled.

That's amazing that it could run 15 minutes. This is a 1GHz with like
512MB RAM running Redhat 8; I hope I can get these converted faster
than that! But if not then we'll just have to live with it- heck a 15
minute coffeebreak 3-4 times a day sounds OK to me no?

Regards, and have a nice summer weekend...

-Gx