Tersus: Load Excel Table

Load Excel TableDate: Jul 10, 2017 Load Excel Table - Load a simple table from an Excel sheet This action retrieves data from multiple rows in a sheet in an Excel file (all non-empty rows following the table's title row) and returns multiple data structures, each containing the content of one of the retrieved rows. Triggers The action's template has three triggers: <File> (non-repetitive trigger [required]): The content of an Excel file (e.g. the output of Read Binary File). <Sheet Name> (non-repetitive trigger [not required]): The name of the sheet in the file from which data is retrieved (if missing, the first sheet in the file is used by default). <Skip Unmatching Rows> (non-repetitive Boolean trigger [not required]): If true, erroneous data rows are ignored (as if they were empty). The default (if the trigger is missing) is not to tolerate erroneous rows. Exits <Rows> (repetitive or non-repetitive exit [required]): The retrieved records (each output item is a data structure containing the content of one of the retrieved rows), or just the first record if the exit is non-repetitive. The data type of <Rows> defines the structure of the loaded table:

The data structure should be "flat" (containing only leaves). This is why we say this action loads a "simple" table.

The name of each leaf in the data structure is the title of the corresponding column in the loaded table. To identify the row in the sheet that contains the table's titles, the action looks for a row containing the names of all leaves (not necessarily in any specific order). For example, if the data type of <Rows> contains two leaves, a textual leaf named Name and a numeric leaf named Age, then we expect the Excel sheet to contain a row in which one cell contains Name and another cell contains Age.

The data type of each leaf defines the type of the data expected in the corresponding column in the table (text, number or date). In the above example, each row in the sheet following the table's titles row may contain some text in the Name column and a number in the Age column.

Empty data rows are ignored (a row in which some cells are empty, but at least one data item does exist, is not considered empty).

An erroneous row is a row in which at least one cell contains data of the wrong type (e.g. a text when a number is expected). Erroneous rows are ignored only if <Skip Unmatching Rows> is true (the default is not to tolerate erroneous rows).

Notice that the table may contain additional columns in addition to the columns specified by <Rows>. These columns are ignored (and specifically, a row containing no data in these rows, but that does contain data in other rows, is considered an "empty row").

Usage Example "Tutorial 10-11/Purchaser/Manage Suppliers/Import Suppliers Data/Select Suppliers Spreadsheet/Import/Extract Spreadsheet Rows" (in stage 10 of the tutorial): Known Bugs This plug-in is using the POI-HSSF package (part of the Apache Jakarta project) for accessing .XLS (Excel 2003 and earlier) files. .XLSX (Excel 2007 and later) files are not supported. You may also encounter problems working with .XLS files not created by Microsoft's Excel (e.g. reading the file might take a very long time). See Also:

To use the full functionality of this web site, JavaScript needs to be turned on.

For best results, use the Firefox browser..

Loading ...