NAME

DBD::Excel - A class for DBI drivers that act on Excel File.

This is still alpha version.

SYNOPSIS

    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();

DESCRIPTION

This is still alpha version.

The DBD::Excel module is a DBI driver. The module is based on these modules:

* Spreadsheet::ParseExcel: reads Excel files.
* Spreadsheet::WriteExcel: writes Excel files.
* SQL::Statement: a simple SQL engine.
* DBI: Of course. :-)

This module assumes TABLE = Worksheet. The contents of first row of each worksheet as column name.

Adding that, this module accept temporary table definition at "connect" method with "xl_vtbl".

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

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

Metadata

The following attributes are handled by DBI 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 DBI attributes are handled by DBD::Excel:

AutoCommit: Always on
ChopBlanks: Works
NUM_OF_FIELDS: Valid after $hSt->execute
NUM_OF_PARAMS: Valid after $hSt->prepare
NAME: Valid after $hSt->execute; undef for Non-Select statements.
NULLABLE: Not really working, always returns an array ref of one's. Valid after $hSt->execute; undef for Non-Select statements.

These attributes and methods are not supported:

    bind_param_inout
    CursorName
    LongReadLen
    LongTruncOk

Additional to the DBI 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 data_sources on setting the directory where Excel files ('*.xls') are searched. It defaults to the current directory (".").
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.

Driver private methods

data_sources

The data_sources method returns a list of '*.xls' files of the current directory in the form "DBI:Excel:xl_dir=$dirname".

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');

TODO

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 SELECT's only, although the basic design of the SQL::Statement module allows joins and the likes.

KNOWN BUGS

There are too many TODO things. So I can't determind what is BUG. :-)

AUTHOR

Kawai Takanori (Hippo2000) kwitknr@cpan.org

  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)

COPYRIGHT

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