NAME

ETL::Pipeline::Input::Excel - Input source for Microsoft Excel spreadsheets

SYNOPSIS

  use ETL::Pipeline;
  ETL::Pipeline->new( {
    input   => ['Excel', iname => qr/\.xlsx$/i],
    mapping => {First => 'A', Second => qr/ID\s*Num/i},
    output  => ['UnitTest']
  } )->process;

DESCRIPTION

ETL::Pipeline::Input::Excel defines an input source for reading MS Excel spreadsheets. It uses Spreadsheet::XLSX or Spreadsheet::ParseExcel, depending on the file type (XLSX or XLS).

METHODS & ATTRIBUTES

Arguments for "input" in ETL::Pipeline

ETL::Pipeline::Input::Excel implements the ETL::Pipeline::Input::File and ETL::Pipeline::Input::File::Table roles. It supports all of the attributes from these roles.

worksheet

Optional. worksheet reads data from a specific worksheet. By default, ETL::Pipeline::Input::Excel uses the first worksheet.

worksheet accepts a string or regular expression. As a string, worksheet looks for an exact match. As a regular expression, worksheet finds the first worksheet whose name matches the regular expression. Note that worksheet stops looking once it finds the first mach.

ETL::Pipeline::Input::Excel throws an error if it cannot find a worksheet with a matching name.

password

Optional. password works with encrypted files. ETL::Pipeline::Input::Excel decrypts the file automatically.

Warning: password only works with Excel 2003 file (XLS). Encrypted XLSX files always fail. Spreadsheet::XLSX does not support encryption.

skipping

Optional. If you use a code reference for skipping, this input source sends a hash reference. You can access the columns by number or letter.

If you pass an integer, the input source completely skips over that many lines.

Methods

run

This is the main loop. It opens the file, reads records, and closes it when done. This is the place to look if there are problems.

ETL::Pipeline automatically calls this method.

SEE ALSO

ETL::Pipeline, ETL::Pipeline::Input, ETL::Pipeline::Input::File, ETL::Pipeline::Input::File::Table

AUTHOR

Robert Wohlfarth <robert.j.wohlfarth@vumc.org>

LICENSE

Copyright 2021 (c) Vanderbilt University Medical Center

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.