Read data from an Excel file
The action is used for transferring data found in a .xlsx file into a form.
The core feature of this action and the complementary Generate an Excel file action is to assign (map) specific process form fields to cell addresses in an Excel spreadsheet.
Reading data from .xlsx files is not supported for the Item list column of the Data row type.
The action configuration window has two tabs: Attachment selection and Mapping.
Attachment selection
The tab allows you to configure the basic parameters of the document to be read.
1. Category
The option allows you to specify the category of the MS Excel files to be read:
- All – all MS Excel files attached to the workflow instance are selected,
- Dynamic – the category can be created dynamically (using Variables editor) or typed in manually. The category should have the following format: ID#Name or ID,
- None – files not assigned to any category are selected.
2. Convert
The field allows you to specify which .xlsx files are to be read. This can be only the oldest or only the newest attachment.
3. Regular expression
The field allows you to search for files to read based on a regular expression. When creating such an expression, you can use the Creator tool run with a separate button available below this field.
4. SQL Query
The field allows for specifying source files based on an SQL query. The query should return a list of attachment IDs from the WFDataAttachmets table.
Select [ATT_ID] from [WFDataAttachmets] where [ATT_Name] = 'file.xlsx';
5. Variables editor
Dynamically generated tree containing all variables that may be used in the current location of Designer Studio. Information on how to use variables in WEBCON BPS, as well as a list of variables can be found here.
Mapping
The tab allows you to specify a list of form fields based on which the corresponding cells of the MS Excel worksheet will be created.
The Excel address should be given by default in the format:
<Worksheet name>!<Cell address or table name>, while the Form field section should include the name of the form field that will be assigned to a specific cell(s) on an Excel spreadsheet.
To correctly address cell "A1", enter: Sheet1!A1
To correctly address a table named "Table1", enter: Sheet1!Table1
For more information on this action and examples of its use, see the article Read data from an Excel file on our technical blog.