Action – Read data from an Excel file

Facebooktwitterpinterestlinkedinmail
Applies to version 8.3.x; Author: Mariusz Burek

While working with workflows, you might come across a scenario where not every employee has access to the intranet, but there exist processes that need to keep information about every user (e.g. Vacation process). Usually, in such cases users who do not have access to the intranet will make their requests through other means, e.g. in an excel file containing the necessary info. In such a case, it is very comfortable to have an action for reading data from an excel file – this way, a person responsible for registering new workflow instances will save a lot of time by not having to copy the data manually.

Animation

1. Action configuration

The action can be found under the ‘Excel, Word & PDF’ group.

1

Fig. 1. Choosing an action from the Step configuration

The action configuration screen is divided into two sections:

  1. Attachments to be processed – Define the method for selecting attachment from which data will be obtained.
  2. Fields mapping configuration – Assign cells from the Excel file to form fields.

2

Fig. 2. Action configuration

In the attachments section, there are 3 different methods for defining which attachments should be processed by the action.

The first and easiest method, is to select an attachment category from the drop-down menu, and then specify whether the newest or oldest attachment from that category should be processed.

The second method is based around a Regular Expression, where you must define what conditions the file name must meet in order to be processed. For example the expression ^Vacation_request will cause the action to use files with names beginning with the phrase “Vacation request

3

Fig. 3. Regular Expression configuration

The third method involves writing a query, which must return the ATT_ID of an attachment – that attachment will then be processed.

4

Fig. 4. Query that returns an attachment ID

The right-hand section is used for assigning cells of the spreadsheet to their corresponding form fields in the process. The left column is used for selecting form fields, in which values found in the spreadsheet will be saved. The right column is used for providing the addresses of cells from the Excel file – the values from these cells will be loaded into the specified form fields. The cell address must be provided in the following format: SpreadsheetName!CellAddress, e.g. Sheet1!A1

4,5

Fig. 5. Mapping form fields to cells of the spreadsheet

2. Action usage

In the example below, the action is used for loading data from vacation requests, which are sent in as Excel files by users.

The first step is to add the Excel file as an attachment to a new workflow instance:

5

Fig. 6. Adding an attachment to the workflow

Next, the registering user must use the path button, on which the action was configured. After using the path, the data will be loaded into fields on the form:

6

Fig.7. The form after data is loaded

3. File used in example

Here is what the Excel file used in the example above, the action configuration (mapping) was based on it:

7

Fig. 8. Sample Excel file

Leave a Reply

Your email address will not be published. Required fields are marked *