Importing and exporting data from the item list

Applies to version 2020.1.3; author: Michał Kastelik

The article is an update of the already existing article, for the original see Importing and exporting data from an item list.

Introduction

WEBCON BPS offers you the ability of importing data to the item list from an Excel file and exporting rows from the item list to an xlsx file. This article describes the configuration of importing/exporting data.

Configuration

To enable import/export, go to the advanced configuration of the form field, and select the option in the “General” tab:

• Allow Excel export
• Allow Excel import

Fig. 1. Enabling the import/export option

After saving changes and refreshing the browser, you will see new options on the form:

• Export
• Import from Excel file

Fig. 2. New buttons on the form

Export the item list to an Excel file

Click the “Export” button – the file with data will be downloaded in the xlsx format. The name of this file is the default form field name with the xlsx extension.

Fig. 3. Import and export button visible on the form field

Open the downloaded file – regardless of the number of columns defined in the item list form field, the data export applies only to the columns that are visible on the form. Columns hidden in a given view will not be exported to the Excel file.

Import data to the item list

First, prepare the file template. The easiest way is to use a file exported from a given form field and then complete it with new information. Go to the “Import settings” tab. There are several options to be completed:

a) Initial column/Initial row – a column/row from which the data import will start

b) Criterion for selection of columns – define the method of assigning columns from the file to columns in the item list (headers or column’s letter)

c) Import type:

• Stop importing on validation error - stops all import when an error is found
• Skip invalid rows - skips rows with errors
• Import invalid cells as blank - in case of an error, data import is performed and invalid cells are blank

d) Required values: 

• Require all columns - requires all columns in the imported file
• Use step configuration - requires values that are required in the configuration of a given step
• Require the first column - requires only the first column in the imported file

Click the “+” button and add columns you want to map from the form field, and then assign the column name or its letter from the Excel file.

Fig. 4. The configuration of the data import

In the “Format” column, define whether the downloaded value will be treated as ID or name. The available options:

• Default – value formatting by default (item list column type)
• ID – column/header is treated as ID
• Name – column/header is treated as the displayed name

As you see in the configuration, data from the column B will be loaded into the “Name of the item…” column, and data from the column A – to the “Amount” column. The import will start from the row 2 from the column A.

The prepared file to be imported:

Fig. 5. Example data in the imported Excel file

After clicking the “Import from Excel file” option and indicating the file, data validation will be performed, and then the summary will be displayed.

Fig. 6. The summary about the import

Imported data visible on the item list:

Fig. 7. The imported data

When importing rows, not all imported fields need to be visible on the form. You can also import columns that are not visible at a given step. Remember that the imported data is not validated for the detection of duplicate rows, and the data format is xlsx (not xls).

Summary

Data import to the item list form field allows you to speed up the process of entering multiple data. The properly prepared data files from other systems allow you to store information in one application form.