Excel – Import and performance for item list

How to import hundreds of rows from an Excel file to the item list? What problems can you encounter when importing data into the item list?

Introduction

WEBCON BPS allows you to export and import data from Excel files. If the amount of data we want to import is small, there are usually no problems. However, real business needs often require the import of hundreds of rows. How to deal with such a challenge then?

Causes of Excel import performance problems

Importing data from an Excel file usually has two main problems:

• Too much data (records and columns) presented on the form
• Importing data into the choice field columns

In the first case, it is simply good practice not to load too much data on the WEBCON BPS form. The WEBCON form is not Excel and it should not be used to fill hundreds of cells. Simply, it is not used for this. Any data changes should be done directly in the Excel file and then re-imported to WEBCON. If it is already necessary to present whole imported data from Excel, it is worth considering importing data to the item list, which is hidden on the form and displaying data from this item list using the data table attribute. There is the option in the data table of paging the presented data.

The second case is a bit more complex. Importing data into the choice field columns means that when you load the form, WEBCON will try to validate every value imported in the database to this item list. In other words, for each value in the item list column, before rendering the form, the application will look for the appropriate value in the data source. If there are several hundred such values ​​on the form, then the form will probably not appear to us at all!

But first things first. Below is a step-by-step guide on how I tested data import based on a SQL-based data source and how we can try to work around it.

Import performance test

Create a data source

First, I created a big data source that is an SQL table that consists of 100.000 records. Below is the SQL script that I used to generate the table with sample data.

CREATE Table ZZ_RandomData
(
Id int identity primary key,
RandomValue nvarchar(max)
)
Declare @Id int
Set @Id = 1
While @Id <= 100000
Begin
Insert Into ZZ_RandomData values ('Value - ' + CAST(@Id as nvarchar(max)) + ' - ' + cast(NEWID() as nvarchar(max)))
Set @Id = @Id + 1
End

Preparing the Excel file for import

Then, based on the table generated above, I prepared an Excel file consisting of 1200 records. I randomly copied several hundred lines from the SQL table and saved them into an Excel file.

Process configuration

I prepared a simple process with one item list called “Import test” to which I want to import 1200 records. To import data I use the “Read data from an Excel file” action that I used on the transition path. Action configuration is as in the screen below.

The first attempt – import to item list

The process is ready, the file is also ready. So it’s import time!

I uploaded a file that had 1200 records and followed the transition path where the action was attached.

Execution time: 6393 ms (value based on WFLogs table). Pretty good! However, after passing the transition path, we will be an unpleasant surprise. The form won’t load and the only thing we can see is the endless loading animation. After a long wait, we will receive an error message.

Why is this happening? Well, as I mentioned before, the values ​​have been imported “as is” from Excel. WEBCON does not validate data during import. Simply puts them into the database as they are in an Excel file. On the form opening, WEBCON tries to validate the data (finds the given value in the data source, checks if the given value is correct and assigns its identifier to each value). In our particular case, we have 1200 text values ​​and a data source that has 100 000 values. So WEBCON tries to search the entire table that has 100 000 values ​​1200 times for the value that is in each row. And it takes a lot of time.

Second attempt – import to item list

We know that the first attempt failed. Can we try to work around this problem somehow? Yes, we can “validate” the values ​​that will be saved directly into the column of the item list. How to do it? For this purpose, we will use an additional “Change item list values” action that will collect identifiers from our data source and immediately save the value in a validated form. Attributes and columns of a “choice field” type in WEBCON BPS are stored as “ID#Name”. That is why it is in this form that we must directly return the values ​​to be saved in the database.

After adding the above-described action and re-importing, everything went well. In this case, the import data and updating the item list took 7949 ms. Thanks to this, the data is already validated and the form is loading immediately.

Displaying imported data from Excel

Okay – we were able to import the data! However, displaying such a large amount of data on the form simultaneously slows down the browser significantly and is very impractical. How can this be remedied? Use the data table to view this data. All you have to do is prepare an appropriate query that will return the imported data and enable the paging option in the form field configuration.

Summary

Unfortunately, importing large amounts of data from Excel into the WEBCON BPS workflow is not a piece of cake. However, if the data source is based on SQL that you can join to WEBCON tables, then you can use the method I described to quickly and efficiently import data into the form.