win32::ole and excel question

[droolin]

History of what is trying to be accomplished:

Currently where I work, we receive a large number of excel documents which are used to manually change pl/sql scripts for updating oracle database's. From my research, I have found that oracle can not read excel documents, least not till 9.0, and then I think only as a csv file type.
In an effort to remove as much manual intervention(I cant beleive they manually change this pl/sql script every time), I am trying to come up with a method to have the excel document saved into a csv format. Then I will either use sql loader, or change the existing scripts to use this file for processing.

In that I kinda, maybe, just a bit know perl. I have done my research in this area to find a solution. I have found that the win32::ole can be used to both read and write excel documents. As I understand things, the ole is an api to the excel engine and should be able to perform all the functions that you normally can manually. All of the examples that I have found have shown individual cells being read or written. I beleive this method would just be loaded with overhead, and would not be the correct path to follow. What I would like to do is have perl via win32::ole open the excel document, and do a save as to an .csv format. I feel that this would be alot less overhead and would be tons faster.

I am not a object programmer, so I always have to bang my head off the wall before I get op modules to work with perl. I was going to start looking for vb scripts that perform this function, and have them converted to perl.

Any sugestions on how I might go about this????

Any help would be greatly appriceated. Thank you in advance.

Dan

[KevinADC]

does excel have a native command for "save as csv"?

[droolin]

I was looking at cpan with regards to the documentation there for the win32::ole. They have an example of a perl script there for excel, and the last line that you see in this is:

# save and exit
       $book->SaveAs( 'test.xls' );

Now, with regard to your question. There are three options to save a file as .csv in excel: Comma Delimited, MS-DOS, and Macintosh.

If I just specify the following:

# save and exit
       $book->SaveAs( 'test.csv');

I am unsure what format it would save in with regard to csv. Shoot, I'm wondering if this will save in pure excel format but just with a different file extension.

Sooooo, the answer to your question is yes. But, multiple ways.

Dan

Hi Dan,

Win32::OLE is not working for me. I'm using cygwin perl. ActiveState's perl might be better.

Both solutions below worked for me

A) have a look here:

http://www.cpan.org/authors/id/A/AN/ANDALE/xls2csv-1.7.pl

B) this one works too:

#!/usr/bin/perl

use strict;
use Spreadsheet::ParseExcel::Simple;

my $filename = shift or die "Usage: xls2csv.pl file.xls\n";

$filename =~ /(.*)\.xls$/i or die "Invalid filename: $filename";
my $prefix = $1;

my $xls = Spreadsheet::ParseExcel::Simple->read($filename);

foreach my $sheet ($xls->sheets)
{
    my $worksheet = $sheet->sheet;
    my $sheetname = $worksheet->{Name};

    open(CSV, ">$prefix-$sheetname.csv") or die "output file: $!";
    while ($sheet->has_data)
    {
        my @row = $sheet->next_row;
        print CSV join(';', @row), "\n";
    }
    close(CSV);
}

__END__

for both solutions, you need to get & install the modules

  Spreadsheet::ParseExcel
  Spreadsheet::ParseExcel::Simple

from cpan.org
these might have dependencies on other modules that need to be resolved

cheers
Ralph