ftos.files.getTabularData

IMPORTANT!  
Starting with v24.3.0, this is renamed from getTabularDataFromFile to ftos.files.getTabularData.

Imports tabular data from a plain text (.csv) or Excel (.xlsx) file stored in the file upload location.

This is a business logic method for business service components.

Syntax

Copy 
function ftos.files.getTabularData(filename : string, options : IFtosWorkflowTabularDataOptions): IFtosWorkflowTabularData;
 
Parameter Type Description
filename string Path to the imported file. The file should be stored in a known folder, eg. Import/test1.csv.
options (optional) IFtosWorkflowTabularDataOptions Delimiters, encoding , and other settings of the imported file in JSON format.

Return Value

Returns a IFtosWorkflowTabularData object that stores the data set imported from the file.

Type Aliases

Delimiters, encoding, and other settings of the imported file in JSON format.

Copy 
{
    delimiters : string[];
    encoding : string;
    excelSheetName : string;
    firstRowRepresentsColumnNames: boolean;
    dataReadingStartsFromRow: number;
    columnsDataTypes: string[]
}
 
Property Returned Type Description
delimiters string Array of delimiters used to separate record values in the data set, such as commas, tabs, or semicolons. Default: [","].
encoding string Text encoding of the data set. Default: UTF-8.
excelSheetName
string If the imported file is in .xlsx format, indicates the Excel sheet from where data is imported. If no value is specified for this parameter, the first sheet in the Excel file is used.
firstRowRepresentsColumnNames boolean If the imported file is in .xlsx format, indicates if the first row cells contain the column names of the table.
NOTE  
The returned tabular data does not include column names. When this property is set to True, the first row in the table will be ignored.
Default: True.
dataReadingStartsFromRow number If the imported file is in .xlsx format, indicates the row where the data import will start, by skipping previous rows.
Default: 1 (all rows are imported).
columnsDataTypes string[] If the imported file is in .xlsx format, indicates the data types for the table columns. Accepted values are: "String", "Number", and "Date". Starting with release 21.1.6, low caps variants "string", "number", and "date" are also accepted.
Default: String.
E.g.: Using ["Number", "Date"] when importing a 3-column table will format the first column as number, the second column as date, and the third column as string.

Stores a data set in tabular format.

Copy 
{
    readonly rowCount : number;
    getAllRows(): IFtosWorkflowTabularDataRow[];
    getRow(index : number): IFtosWorkflowTabularDataRow;
    saveToTemporaryTable(table : string): void;
    saveToTemporaryTable(table : string, settings : IFtosSaveToTemporaryTableOptions): void;
}

Properties

Property Returned Type Description
rowCount (readonly) number Number of rows in the data set.

Methods

Returns an array of IFtosWorkflowTabularDataRow objects, each containing a record (row) in the data set.

Returns a IFtosWorkflowTabularDataRow object containing a specific record (row) in the data set.

Parameter Type Description
index number Row number of the record you wish to retrieve.

Saves the data set to a temporary table.

Parameter Type Description
table string Name of the temporary table where you wish to save the data set.

Saves the data set to a temporary table.

Parameter Type Description
table string Name of the temporary table where you wish to save the data set.
settings IFtosSaveToTemporaryTableOptions Options for including the table header and skipping rows.

Object containing a specific record (row) in a IFtosWorkflowTabularData data set.

Copy 
{
    readonly columnCount : number;
    getAllColumns(): string[];
    getColumn(index : number): string;
}

Properties

Property Returned Type Description
colunCount(readonly) number Returns the number of values (columns) in the record (row).

Methods

Returns an array of strings, each containing a value (column) of the record (row).

Returns a string containing a specific value (column) of the record (row).

Parameter Type Description
index number Column number of the value you wish to retrieve.

Options for including the header and skipping rows when saving a data set to a temporary table.

Copy 
{
    hasHeader : boolean;
    skip : number;
}
 
Parameter Type Description
hasHeader boolean Set to True if the first row is the table header, False otherwise.
skip number Set the number of rows from the start of the table you wish to skip when saving the data set to the temporary table.

The width of the table is determined by the maximum number of columns from the data set's rows. If the table has a header, missing column names will be computed as c{index}, eg. c5 if the header for the 5th column is empty.

Examples

In this example:

  • We extract all the rows from a comma/semicolon separated, UTF-16 encoded CSV file in a variable called rows.

  • We iterate over each row and ftos.logging.log it in the trace_roll.log file.

Copy 
let rows = ftos.files.getTabularData('planning.csv', {delimiters : [',',';'], encoding : 'UTF-16'}).getAllRows();

rows.forEach(writeLog);

function writeLog(value){
    log(value.getAllColumns())
}