Loading data from a CSV file into SAP Build App VCF backend

[fvettore64]

Update July '23

The capability to import data to VCF from a file has been added to SAP Build Apps on July 2023. Here the link to the official documentation: https://help.sap.com/docs/build-apps/service-guide/data-browser#data-import 

But I think that some of the techniques described in this blog are still interesting and can be reused in the application you're developing.

 

SAP Build Apps provides the capability to create and deploy application backends. This capability is named Visual Cloud Functions (VCF) and here you can find a good documentation for this feature.

At present, VCF does not provide the ability to massively load data into the backend using, for instance, a CSV file. Few weeks ago, to test and run an application I developed with SAP Build Apps, I needed to load 200 records into the VCF backend. Doing that manually, would have been time expensive and error prone. Then I decided to develop a utility to make this task easy, fast and repeatable. Writing this blog, I hope to help people that are facing a similar situation.

I developed a quite simple app to load data from a CSV file into a VCF backend and this solution can be very useful until a load utility will be provided for VCF.

Here a detailed explanation of an app you can easily develop for loading data into the VCF backend:

First of all, let’s start creating a simple VCF backend for managing an entity named Person with three attributes:

• firsName : text


• lastName : text


• age : number

And then let’s create an Excel file, based on the same schema, that contains some data to be loaded into the VCF backend:

The names of the columns must correspond to the names of the attributes of the VCF entity.

The Excel file can be easily exported as CSV file, using the Save As function provided by Excel. Here you can see the content of the exported file :

Now let’s implement our CSV Loader application, using SAP Build Apps. As a first step, a connection to the VCF backend must be established. This blog post explains how to complete this task (please see the Enabling the Backend from the UI section).

Before starting the design of our application, we need to install a couple of components from the Marketplace. Those components are needed to manage the files; this can be done just clicking on Marketplace:

and searching for "file":

and selecting and installing the two required components: “Pick files” and “Convert files to base64”.

Now we can start building our app, putting into a Container two buttons: a button for choosing the file selection and another one for running the import of the data from the selected CSV:

Before adding the logic to the buttons, we need to define two variables (as an App Variable or a Pager Variable):

Variable name: decodedText

Variable Type: text.

This variable will be filled with the content of the CSV file, after being decoded from the base64 format.

Variable Name: personList

Variable Type: List of Object

The objects listed must be defined with the same properties of the schema (firstName, lastName, age) we've used for VCF. The names of the properties must be exactly the same we’ve in the headers of the CSV files:

Here following the logic to be defined for the Select a File Button:

Let's better describe each single step:

Pick Files: this step opens a window for selecting a file. We can configure this component (in the right panel) with the allowed file formats (plainText files only) and preventing to select multiple files.

Convert file to base64: this step reads a file and encodes it to the base64 format. As input we need to provide the path of the selected file. Here the formula to be used:

outputs["Pick files"].files[0].path

 

Set app variable: this step sets the value of the decodedText variable to the content of the CSV file, after decoding it from the base64 format. We can use the formula:

DECODE_BASE64(LEFT_STRIP(outputs["Convert file to base64"].base64, 21))

JS: this step is the heart of our data loader and it is implemented as a javascript routine. The routine parses the CSV file and creates as output a list of objects. The input is the decodedText variable and the output, named result, must be a list of generic Objects (no need to define their structure). Here the code:

try {

  

  a = inputs.input1;

  

  var headers = []

  var data = []

  var lines = inputs.input1.split("\r\n");



for (var i = 0; i < lines.length; i++) {

                    var cells = lines[i].split(";");

                    var lineData = {};

                    for(var j=0;j<cells.length;j++){

                        if(i==0){

                            var headerName = cells[j].trim();

                            headers.push(headerName);

                        }else{

                            var key = headers[j];

                            if(key){

                                lineData[key] = cells[j].trim();

                            }

                        }

                    }

                    //skip the first row (header) data

                    if(i!=0){

                        data.push(lineData);

                    }

                }

  return { result: data };

}

catch (err) {

    const error = {

      code: 'unknownError',

      message: 'Something went wrong.',

      rawError: err,

    }

  

    return [1, { error }]



}

 

Set App Variable: this step sets the personList variable to the result of the previous step, using a formula:

outputs["Function"].result

In order to check all the records to be loaded into the VCF backend, we can add, to the home page of our app, a Card that displays the list of records:

The Title of the Card can be set with a formula, to show the full name as a concatenation of firstName + lastName:

repeated.current.firstName + ' ' + repeated.current.lastName

And the Content of the Card can be set to show the age:

The logic for the Select a File button is now completed and we can define the logic for the Load all button:

 

Here a detailed description for each step:

IF Condition: this step is used to define a cycle on all the items included in the personList variable. It contains a very simple formula telling that the next step must be executed only if the personList variable contains at least one item:

COUNT(appVars.personList) > 0

When the personList variable is empty, the logic stop running.

Create Record: this step inserts a record into the VCF. The record to be created corresponds to the first item in the variable personList, identified by index #0. Here you can see the binding to be configured:

Set app variable: this step executes a simple formula to remove the first item from the personList variable:

REMOVE_ITEMS_AT(appVars.personList, 0)

After executing this step, the second item of the personList variable takes the place of the first one, that has been just removed. The cycle, controlled by the IF step, will be repeated until all the items have been processed and the personList variable becomes empty. At this point the loading logic is completely executed.

The basic app is now completed and we can test it. Let's go to Launch and Open the Preview Portal:

Then Open the web preview and select the app we've just created. The home page appears, showing the two buttons we defined:

When clicking on the Select a File button, a window appears and a file can be selected. Here we must select and open the CSV we created at the beginning:

The application will read the file and update the home page, displaying all the records to be loaded into the VCF backend:

After verifying that everything is correct, we can click the Load all button. You'll see that the records will be removed one by one from the displayed list, as soon as a new record is created into the VCF backend.

Now we just need to check if the records have been inserted into the VCF backend. Le's open the VCF backend from the Lobby and browse the data:

and that's done !

All the records have been inserted into the VCF backend.

With a limited number of modification, this template app can be adapted to load whatever VCF you want to design. The only changes that are strictly necessaire are:

• the definition of the structure of personList variable (and probably you also want to rename it), to match your VCF schema


• the definition of the Card in the home page should be adapted to display the list of your items


• the Create record step should be changed to bind the structure of your item.

Just a couple of additional considerations:

• the card I added to display the records loaded from the file is useful for the test of the application, but it slows down the loading. It can be removed after successfully testing the app. In that case you should add ad the end of the loading an alert box that confirms the completion of the task;


• this approach has a relevant bottleneck: all the records to be loaded are stored in a single variable (the personList, in my example). The system is not able allocate an huge amount of memory for a single variable and this limits the number of records that can be loaded from a file. If you need to load more than 50 - 100 records (depending on the number of attributes), you should prepare multiple files and run the loading process for each of them. Or you can modify the app for being able to select more that one file and then to process sequentially the load from all of them.

Now that the logic of the application is clear, you will be able to add more features into the apps you're developing with SAP Build Apps.

For instance:

• the ability to load a single item from the list can be added with few clicks,


• the ability to remove records from a VCF backend, one by one or massiviely,


• the CSV file parsing code can be reused in other apps,


• and much more ...

In another article I'll explain how to run the reverse task: how to save data from a VCF backend to a CSV file.

And that's all for today!  Now you can continue to follow the SAP Build Apps environment Topic page (https://community.sap.com/topics/build-apps), post and answer questions (https://answers.sap.com/tags/6cfd8176-04ae-4548-8f38-158456e1a47a), and read other posts on the topic (https://blogs.sap.com/tags/6cfd8176-04ae-4548-8f38-158456e1a47a/).

 

Have a good time with SAP Build Apps! And please continue following my profile for new blogs and let me have your feedbacks.