/ 
DBIx-Spreadsheet-0.04
River stage zero No dependents
/ DBIx::Spreadsheet

NAME

DBIx::Spreadsheet - Query a spreadsheet with SQL

SYNOPSIS

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.

WARNING

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.

METHODS

DBIx::Spreadsheet->new

my $wb = DBIx::Spreadsheet->new(
    file => 'workboook.ods',
);

Options

->dbh

my $dbh = $wb->dbh;

Returns the database handle to access the sheets.

->tables

my $tables = $wb->tables;

Arrayref containing the names of the tables. These are usually the names of the sheets.

->table_names

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;

SUPPORTED FILE TYPES

This module supports the same file types as Spreadsheet::Read. The following modules need to be installed to read the various file types:

TO DO

  • 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.

SEE ALSO

DBD::CSV

REPOSITORY

The public repository of this module is https://github.com/Corion/DBIx-Spreadsheet.

SUPPORT

The public support forum of this module is https://perlmonks.org/.

BUG TRACKER

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.

AUTHOR

Max Maischein corion@cpan.org

COPYRIGHT (c)

Copyright 2019 by Max Maischein corion@cpan.org.

LICENSE

This module is released under the same terms as Perl itself.