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]

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:arseExcel, something like this:

--

#!/usr/bin/perl -w
use strict;
use Spreadsheet:arseExcel;
my ($file) = (@ARGV);
my $dest = $file;
my $xls = Spreadsheet:arseExcel::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]

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:arseExcel;

 

[A. Sinan Unur]

(e-mail address removed) (Sara) wrote in @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:

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

Sinan

 

[Sara]

(e-mail address removed) (Sara) wrote in @posting.google.com:


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...

NIH?

Not Invented Here
_ _ _

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:

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

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

 

[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

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

--

#!/usr/bin/perl -w
use strict;
use Spreadsheet:arseExcel;
my ($file) = (@ARGV);
my $dest = $file;
my $xls = Spreadsheet:arseExcel::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