DBD::Excel(3pm)

Usage

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

Overview

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 the first row of each worksheet are used as column name.

Adding that, this module accepts a temporary table definition in the "connect" method with the "xl_vtbl" option.

Example:

    my %options = (
        xl_vtbl => { # define temporary tables here
            TESTV => { # temporary table name => options
                sheetName => 'TEST_V', # actual Excel sheet name
                ttlRow    => 5,
                startCol  => 1,
                colCnt    => 4,
                datRow    => 6,
                datLmt    => 4,
            }
        }
    );
    my $dbh = DBI->connect("DBI:Excel:file=dbdtest.xls", undef, undef, \%options);

For more information please refer to the file sample/tex.pl which is 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 "$sth->execute"

NUM_OF_PARAMS: Valid after "$sth->prepare"

NAME: Valid after "$sth->execute"; "undef" for Non-Select statements.

NULLABLE: Not really working, always returns an array ref of one's. Valid after "$sth->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 attributes:

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($drh) = DBI->install_driver("Excel");
    my(@list) = $drh->data_sources( { xl_dir => '/usr/local/xl_data' } );

list_tables: This method returns a list of sheet names contained in the "$dbh-"{file}>. Example:

my $dbh = DBI->connect("DBI:Excel:file=test.xls"); my @list = $dbh->func('list_tables');

Todo

More tests: First of all...

Type and Format: The current version not support date/time and text formatting.

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 determine what is BUG. :-)

Releases

12 releases since Debian 3.0 (Woody); the text changed 7 times.