parsing Excel spreadsheets and workbooks

[Jeff Zucker]

Tom Keller wrote:
>
> >>> "Jeff Zucker" <jeff at vpservices.com> - 4/24/01 5:32 PM >>>
> Tom Keller wrote:
> >
> > Looks like I'm going to be parsing some Excel workbooks and enclosed worksheets.
> > The appropriate module seems to be Spreadsheet::ParseExcel.
> 
> Could be.  Depends on what you mean by "parsing".  You might also think
> about treating the spreadsheet data as a series of ODBC data sources and
> get at it through DBI and DBD::ODBC.  If that sounds like something
> you'd want to do and you need help on that end, give a holler.
>
> 
> It does Jeff: HOLLER.
> Specifically, how would I identify and read the different worksheets within a workbook?

Hi Tom,

AFAIK, the easiest way is to use named ranges.  So suppose that you have
a range on sheet #1 called "parts" and a named range on sheet #2 named
"transactions", you would access all the rows of "transactions like
this:

use DBI;
my $path        = 'd:/htdocs/cgi-bin/test_snippets/';
my $driver      = 'ODBC:driver=Microsoft Excel Driver (*.xls)';
my $workbook    = 'dbi-excel-2.xls';
my $named_range = 'transactions';
my $dbh = DBI->connect(
    "dbi:$driver;Dbq=$path$workbook",,,{RaiseError=>1}
);
print $dbh->selectrow_array("SELECT * FROM $named_range");

Alternatively, you might be able to specify an entire worksheet as a
table by using the ODBC manager in windoze to create a DSN for a
specific worksheet within a specific workbook but I haven't tried that.

Hope that helps!

-- 
Jeff
TIMTOWTDI