The "Read data from an Excel file" action

Applies to version: 2021.1.x and above; author: Konrad Wojtycza

Introduction

This article describes the “Read data from an Excel file” action that allows you to import data from an Excel file to the form. You can use this action in the following scenarios:

• Part of the information entered into WEBCON BPS is filled out by people who do not have access to the company’s internal network (temporarily or permanently). You can prepare an Excel file and indicate data to be filled out.  
• Importing the document templates with duplicate values from Excel files.
• Importing Excel files that contain values calculated using a spreadsheet.

We have created the simple “Leave requests” workflow with the “Read data from an Excel file” action. It allows an employee (who temporarily does not have access to the company’s intranet) to submit a leave request.

Fig. 1. The "Leave requests" workflow

Configuration of the action

The action was added at the “Register request” step on the “Import data from attachment” path.

Fig. 2. Selecting the action from the "Excel, Wrod & PDF" section

In the advanced configuration of this action, there are two tabs:

• Attachment selection - selecting the attachment from which data will be downloaded
• Mapping – mapping cells from the Excel file to form fields

Fig. 3. Configuration of the action - the Attachment selection tab

Attachment section tab:

• Category – allows you to select an attachment based on the category. In the “Convert” file, a user indicates whether the newest or the oldest attachment should be used in the action.
• Regular expression – allows you to enter a regular expression in which you can define parameters that should be met by the downloaded file. For example – the ^Leave_request.* expression means to download a file whose name starts with “Leave_request”.

Fig. 4. Sample regular expression

• SQL query – allows you to create a query that returns the ATT_ID of the attachment from which data is to be downloaded.

Fig. 5. Configuration of the SQL query returning the attachment

Mapping tab – in this section a user assigns sheet cells (Excel address column) to the corresponding form fields (Form field column). The cell address must be configured according to the SheetName!CellAddress rule – e.g. Sheet1!A1. A dynamic selection of cells and sheets is not possible.

Fig. 6. The mapping process

Example of use

In the presented example, the action is used to download the Excel file with leave requests. Cells to be filled out are marked in yellow.

Fig. 7. The Excel file with leave requests

The same cells have been mapped with the form fields on the form in the configuration of the "Read data from an Excel file" action. The first step is to add an attachment to the workflow.

Fig. 8. Attachment added to the workflow

Then the user must follow the path where the action was set up. In the example above, it is the "Import data from attachment" path.  After following the path, the data will be downloaded to the form.

Fig. 9. The form after following the "Import data from attachment" path

Summary

The “Read data from an Excel file” action allows for importing data from a previously prepared Excel file. It can be especially useful when the process requires entering many slightly different documents – thanks to this, you can quickly complete fields on the WEBCON BPS form. It also can be used when you do not want to create accounts and give access to internal company resources to external people.