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