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

• 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

->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:

• Text::CSV_XS - CSV files

• Spreadsheet::ParseXLS - Excel XLS files

• Spreadsheet::ParseXLSX - Excel XLSX files

• Spreadsheet::ParseSXC - Staroffice / Libre Office SXC or ODS files

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.

cpanm DBIx::Spreadsheet

perl -MCPAN -e shell
install DBIx::Spreadsheet