Uploading data from a CSV file as an array into a database

On this page:

1. Business process modeling
2. Form modeling
- 2.1. Data upload form
- 2.2. Form for data signing
3. Example of a business process in the Officer Portal

The possibility of loading data as an array to a database allows you to create business processes that the registry users can use for adding a data array as a single file, for example, filling the registry directories or loading additional data.

In order to model a business process with the possibility of importing data from a CSV file as an array into a database, perform the following actions.

Use out-of-the-box BPMN diagram of the test business process and the Download diploma data (csv file) and Sign diploma data (csv file) forms to see more context for further use.

1. Business process modeling

Model the business process according to the instructions in the link.

Prerequisites:: Before modeling the process and configuring the Batch creation of entities in data factory v2 delegate, make sure that the /element-templates folder of the Camunda Modeler application contains the dataFactoryConnectorBatchCreateDelegateV2.json file.

Otherwise, use the delegate download instructions available at the link.

Using the Batch creation of entities in data factory v2 delegate is possible only for tables that have the ext:bulkLoad="true" attribute inside the <createTable> tag.

Create the user task using a form that contains the corresponding file component for loading.
- Enter the task name in the Name field.
- In the Form key field, enter the service name of the form.
- In the Assignee field, enter ${initiator} — the user who initiated the execution of this business process.
Create the user task using the form for data signing.
- Enter the task name in the Name field.
- In the Form key field, enter the service name of the form for data signing.
- In the Assignee field, enter ${initiator} — the user who initiated the execution of this business process.
- in the Form data pre-population field, enter the ${submission('addCsvFileActivity').formData} variable.
Create the script task to prepare the data for writing.
- Enter the task name in the Name field.
- In the Script Format field, select groovy.
- In the Script Type field, select InlineScript.
- In the Script field, enter the script itself, for example, in our case:
  
  set_transient_variable('payload', submission('signCsvFileActivity').formData.prop('csvFile').elements().first())
Create the service task for data saving.
- Click Open Catalog and select Batch creation of entities in data factory v2 delegate, then click Apply.
- Enter the task name in the Name field.
- In the Resource field, enter the resource — the name of the endpoint for the table, where the data will be stored.
- In the Upload type field, select CSV.
- In the Payload field, enter data you want to create — ${payload}.
- in the X-Access-Token field, enter the user’s system access token under which the operation is performed, for example: ${completer('signCsvFileActivity').accessToken}.
- In the X-Digital-Signature source field, enter the source of the digital signature, for example: ${sign_submission('signCsvFileActivity').signatureDocumentId}.
- In the Result variable field, enter any name for the output parameter (by default, response).

2. Form modeling

Model the forms according to the instructions in the link.

2.1. Data upload form

Enter Form’s business name and Form’s service name.

Go to the Builder tab. Select the Updated section. Select the Content component and drag it to the modeling panel.

loading data from csv 07

In the form for file uploading, we recommend to model a prompt for filling out the file (for example, using the Content component) where it is necessary to describe the requirements for the uploaded file.

Add a description of the requirements to the file and click Save.

loading data from csv 08

The following restrictions for the file are currently in place:

It is possible to download only one file at a time.
The file format is CSV only.
Encoding is UTF-8.
The maximum number of entries is 50.
The maximum file size is 100 MB. This is the limit for data processing for saving or editing (general input limit).

Requirements for the data in the file:

Data separator in the file is ";" (semicolon).
The format of the fields in the file must comply with that of the fields in the database.
If data is uploaded to the directory, then each record must contain a unique key in the corresponding format.

In the Updated section, select the File component and drag it to the modeling panel.
Enter the name of the component in the Label field.
Go to the File tab. In the File Pattern field, enter the *.csv value. Below in the form, set the minimum and maximum permissible file size in the corresponding fields: File Minimum Size and File Maximum Size (maximum 1 MB).
Go to the Validation tab. Select the Required checkbox and enter the name of the table to which data in the Resource for validation field will be written.
Go to the API tab. In the Property Name field, enter csvFile. Click Save to save the changes.
Add and configure the Button component.
In the top right-hand side of the page, click Create form.

2.2. Form for data signing

Enter Form’s business name and Form’s service name.
Go to the Builder tab. Select the Updated section. Select the File component and drag it to the modeling panel.
Configure the component in the same way as in the previous section, except that the form must be view-only. To do this, in the Display tab, select the Disabled checkbox and change the name of the component.
Add and configure the Button component.
In the top right-hand side of the page, click Create form.

Apply the changes to the modeled forms. To do that, go to the Version view section and click the Apply changes to master version button.

loading data from csv 21

3. Example of a business process in the Officer Portal

Go to the corresponding service modeled in the Officer portal in the Available services menu.

Add a file that meets the requirements and click Next.

loading data from csv 23

During upload, the pre-validation of the data in the CSV file is triggered immediately on the form.

In the event of an error, the system warns the user about format inconsistency even before switching to the UI form for signing the QES data.

There are currently 3 types of CSV file validations when uploading data to the database:

Validation of the format (extension) and encoding.
- Format is CSV; encoding is UTF-8.
Validation of the number of entries in a file.
- The maximum number of entries in a file is 50.
Validation of the uploaded data structure.
- The separator is a semicolon (;).
- The column names in the file must match the column names in the database.
- The value format of the fields must match the format of the database.

At the data signing phase, the pre-uploaded file is view-only. At the bottom of the page, fill in the key parameters and click Read.
To apply the digital signature, click Sign.
The executed business process for downloading data from the form as an array (csv file) is registered in the section My services → `Provided services.