DBD::Excel (3pm)

    use DBI;
    $hDb = DBI->connect("DBI:Excel:file=test.xls")
        or die "Cannot connect: " . $DBI::errstr;
    $hSt = $hDb->prepare("CREATE TABLE a (id INTEGER, name CHAR(10))")
        or die "Cannot prepare: " . $hDb->errstr();
    $hSt->execute() or die "Cannot execute: " . $hSt->errstr();
    $hSt->finish();
    $hDb->disconnect();

This is still alpha version.

The DBD::Excel module is a \s-1DBI\s0 driver. The module is based on these modules:

* Spreadsheet::ParseExcel

reads Excel files.

* Spreadsheet::WriteExcel

writes Excel files.

* SQL::Statement

a simple \s-1SQL\s0 engine.

* \s-1DBI\s0

Of course. :-)

This module assumes \s-1TABLE\s0 = Worksheet. The contents of first row of each worksheet as column name.

Adding that, this module accept temporary table definition at \*(L"connect\*(R" method with \*(L"xl_vtbl\*(R".

ex.

    my $hDb = \s-1DBI-\s0>connect(
            \*(L"DBI:Excel:file=dbdtest.xls\*(R", undef, undef,
                        {xl_vtbl =>
                            {\s-1TESTV\s0 =>
                                {
                                    sheetName => '\s-1TEST_V\s0',
                                    ttlRow    => 5,
                                    startCol  => 1,
                                    colCnt    => 4,
                                    datRow    => 6,
                                    datLmt    => 4,
                                }
                            }
                        });

For more information please refer sample/tex.pl included in this distribution.

\$1

The following attributes are handled by \s-1DBI\s0 itself and not by DBD::Excel, thus they all work like expected:

Active ActiveKids CachedKids CompatMode (Not used) InactiveDestroy Kids PrintError RaiseError Warn (Not used)

The following \s-1DBI\s0 attributes are handled by DBD::Excel:

AutoCommit

Always on

ChopBlanks

Works

\s-1NUM_OF_FIELDS\s0

Valid after \*(C`$hSt->execute\*(C'

\s-1NUM_OF_PARAMS\s0

Valid after \*(C`$hSt->prepare\*(C'

\s-1NAME\s0

Valid after \*(C`$hSt->execute\*(C'; undef for Non-Select statements.

\s-1NULLABLE\s0

Not really working, always returns an array ref of one's. Valid after \*(C`$hSt->execute\*(C'; undef for Non-Select statements.

These attributes and methods are not supported:

bind_param_inout CursorName LongReadLen LongTruncOk

Additional to the \s-1DBI\s0 attributes, you can use the following dbh attribute:

xl_fmt

This attribute is used for setting the formatter class for parsing.

xl_dir

This attribute is used only with \*(C`data_sources\*(C' on setting the directory where Excel files ('*.xls') are searched. It defaults to the current directory (\*(L".\*(R").

xl_vtbl

assumes specified area as a table. See sample/tex.pl.

xl_skiphidden

skip hidden rows(=row height is 0) and hidden columns(=column width is 0). See sample/thidden.pl.

xl_ignorecase

set casesensitive or not about table name and columns. Default is sensitive (maybe as SQL::Statement). See sample/thidden.pl.

data_sources

The \*(C`data_sources\*(C' method returns a list of '*.xls' files of the current directory in the form \*(L"DBI:Excel:xl_dir=$dirname\*(R". If you want to read the subdirectories of another directory, use my($hDr) = DBI->install_driver("Excel"); my(@list) = $hDr->data_sources( { xl_dir => '/usr/local/xl_data' } );

list_tables

This method returns a list of sheet names contained in the $hDb->{file}. Example: my $hDb = DBI->connect("DBI:Excel:file=test.xls"); my @list = $hDb->func('list_tables');

More tests

First of all...

Type and Format

The current version not support date/time and text formating.

Joins

The current version of the module works with single table \s-1SELECT\s0's only, although the basic design of the SQL::Statement module allows joins and the likes.

• There are too many \s-1TODO\s0 things. So I can't determind what is \s-1BUG\s0. :-)

Kawai Takanori (Hippo2000) [email protected]

Homepage: http://member.nifty.ne.jp/hippo2000/ (Japanese) http://member.nifty.ne.jp/hippo2000/index_e.htm (English)

Wiki: http://www.hippo2000.net/cgi-bin/KbWiki/KbWiki.pl (Japanese) http://www.hippo2000.net/cgi-bin/KbWikiE/KbWiki.pl (English)

\s-1DBI\s0, Spreadsheet::WriteExcel, Spreadsheet::ParseExcel, SQL::Statement

Copyright (c) 2001 \s-1KAWAI\s0,Takanori All rights reserved.

You may distribute under the terms of either the \s-1GNU\s0 General Public License or the Artistic License, as specified in the Perl \s-1README\s0 file.