DBIx::Spreadsheet - Query a spreadsheet with SQL
my $sheet = DBIx::Spreadsheet->new( file => 'workbook.xlsx' );
my $dbh = $sheet->dbh;
my @rows = $dbh->selectall_arrayref(<<'SQL');
select *
from sheet_1
where foo = 'bar'
SQL
This module reads a workbook and makes the contained spreadsheets available
as tables. It assumes that the first row of a spreadsheet are the column
names. Empty column names will be replaced by col_$number
. The sheet and
column names will be sanitized by Text::CleanFragment so they are
conveniently usable.
The parsing and reformatting of cell values is very hacky. Don't expect
too much consistency between ods
and xlsx
cell formats. I try to
make these consistent, but this is currently a pile of value-specific hacks.
my $wb = DBIx::Spreadsheet->new(
file => 'workboook.ods',
);
- file - name of the workbook file. The file will be read using Spreadsheet::Read
using the options in
spreadsheet_options
. - spreadsheet - a premade Spreadsheet::Read object
- spreadsheet_options - options for the Spreadsheet::Read object
my $dbh = $wb->dbh;
Returns the database handle to access the sheets.
my $tables = $wb->tables;
Arrayref containing the names of the tables. These are usually the names of the sheets.
print "The sheets are available as\n";
for my $mapping ($foo->table_names) {
printf "Sheet: %s Table name: %s\n", $mapping->{sheet}, $mapping->{table};
};
Returns the mapping of sheet names and generated/cleaned-up table names. This may be convenient if you want to help your users find the table names that they can use.
If you want to list all available table names, consider using the DBI catalog methods instead:
my $table_names = $dbh->table_info(undef,"TABLE,VIEW",undef,undef)
->fetchall_arrayref(Slice => {});
print $_->{TABLE_NAME}, "\n"
for @$table_names;
This module supports the same file types as Spreadsheet::Read. The following modules need to be installed to read the various file types:
- Text::CSV_XS - CSV files
- Spreadsheet::ParseXLS - Excel XLS files
- Spreadsheet::ParseXLSX - Excel XLSX files
- Spreadsheet::ParseSXC - Staroffice / Libre Office SXC or ODS files
-
Create DBD so direct usage with DBI becomes possible
my $dbh = DBI->connect('dbi:Spreadsheet:filename=workbook.xlsx,start_row=2');
DBIx::Spreadsheet will provide the underlying glue.
The public repository of this module is https://github.com/Corion/DBIx-Spreadsheet.
The public support forum of this module is https://perlmonks.org/.
Please report bugs in this module via the RT CPAN bug queue at https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-Spreadsheet or via mail to dbix-spreadsheet-Bugs@rt.cpan.org.
Max Maischein corion@cpan.org
Copyright 2019 by Max Maischein corion@cpan.org
.
This module is released under the same terms as Perl itself.