Unloading data from a SAP Build Apps VCF backend to CSV files

[fvettore64]

In my previous blog, I showed how data from a CSV files can be loaded into a SAB Build Apps Visual Cloud Functions (VCF) backend.

Today I want to cover another use case: you developed a SAP Build Apps application and you ran it for some times in a BTP subaccount, saving valuable data into the VCF backend. Now you need to migrate you application to another subaccount and you want to move also the data you saved into the backend.

As I mentioned in my previous blog, at present, VCF does not provide the ability to massively load or unload data using, for instance, a CSV file.

In this article I'll explain how data can be unloaded from your VCF backend to CSV files. Data saved into CSV files can be used for for migration purposes but also for different activities: for instance you can load them into an Excel sheet and perform massive manipulations on them.

Let's start the development of our application just creating a new project, enabling the BTP authentication and installing the integration with the VCF backend you want to use. This blog post explains how to complete this task (please see the Enabling the Backend from the UI section).

Then we need to install an additional component from the Marketplace, for managing the download of the CSV file. This can be done just clicking on Marketplace:



and searching for “file”:



and selecting and installing the required component: “Download Base64 text as file (web only)”.

On the empty UI Canvas, we just need to add a button and, maybe, some text providing an explanation:

We define the variables needed by the processing logic:

Variable Name: ObjectLists

Variable Type: Data Variable

This variable must be defined for reading a collection of records from the VCF backend:

The default Data Variable Logic must be removed, because we want our logic to be executed after pressing the button, not when loading the initial page.

Just another variable needs to be declared:

Variable Name: convertedText

Variable Type: text.

Here following the logic to be defined for the Unload Data button:

Let's review each single steps:

Get Record Collection: this step reads all the record we've in the VCF backend.

Set Data Variable: this step fills the ObjectList data variable with all the record read in the previous step.

JS: this step is the heart of our unloading process and it is implemented as a JavaScript routine. The routine scans all the records from the ObjectList data variable (in input) and it generates a text variable as output that contains all the records in CSV format. Here following the code I used:

try {

    a = inputs.input1;

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

                  delete a[i].id

                }

      

    let topLine = Object.keys(a[0]).join(";");

    const lines = a.reduce( (acc, val) => 

    acc.concat( Object.values(val).join(";") ), [] );

    const csv = topLine.concat(`\r\n${lines.join("\r\n")}`); 

    return { result: csv };

            

}



catch (err) {

    const error = {

      code: 'unknownError',

      message: 'Something went wrong.',

      rawError: err,

    }

  

    return [1, { error }]



    }

Please notice that on row #4 I've removed the property id from all the records. This is because I want my CSV file does not contain the UUID generated automatically by VCF when loading data. If you want to have this information in you CSV file, you should remove the rows #3, #4 and #5.

If you don't like to use the JavaScript component, you can replace it defining some more variables and using one or more Set Variable components with some formulas: the JavaScripts functions I used here (join, concat, reduce, keys) are also available in SAP Build Apps formulas.

SAP Build Apps provides a rich set of functions for manipulating strings, lists and objects.

Set Page Variable: this steps sets the variable convertedText to the output of the previous step, encoding it to the base64 format. I used the following formula:

ENCODE_BASE64(outputs["Function"].result)

Download Base64 text as file (web only): this step downloads the CSV file to your PC. You should configure it providing as input the convertedText variable, defining the name assigned to the file when downloaded (in my case: Output.csv) and setting the MIME Type to Plain Text:

Our app is now ready. You can run it just clicking on LAUNCH, opening the preview portal and selecting the Web Preview option.

After clicking the Unload Data button, the CSV file will be downloaded to your PC (maybe your should provide some authorisation, depending on the settings of your browser).

The CSV file can be edited using a text editor or Excel and it can be uploaded to the VCF backend of another BTP subaccount, using the approach I described in my previous blog.

If your VCF backend contains few records (50 - 100), this application is enough for your requirements. But if you need to unload more records, it won't work: the variable we declared for internally storing the records after reading them from the backend (ObjectList), becomes a bottleneck, because the system is not able to allocate to that variable all the memory required to handle an huge number of records.

In that case, we need to change the application: we can download all the records we've in our VCF backend partitioning the unload process into a sequence of work units. And this can be done using the Paging capability provided by the VCF backend: it is possible to configure a Get Record component telling the number of record we want to read for each page and the setting the number of the page we want to read. Then the approach consists in reading from the VCF backend a sequence of pages and downloading a CSV file for each page that has been read.

The logic of the extended app looks like that:

As a first step, a new index variable (type = number) needs to be declared and set to 1. This variable will be used to identify the page number to be read from the backend during each iteration.

In the next step, named FirstRead, we access the backend for getting the first page, using a Get Record component. In my example, I decided to set the page size to 50 records.

The Get Record component must be configured, setting the Paging property as an object:

and that object must be configured with the number of records per page (50 in my example), the page number (corresponding to the content of the index variable) and including the Total Count as output variable:

Then the result of this step is stored, as usual, to a data variable and Total Count output  is used to calculate the total number of pages to be read from the VCF backend. We can use to following formula for setting the value of the numPages variable:

INTEGER(outputs["FirstRead"].totalCount /50) + 1

At this point a cycle starts performing the following steps:

• convert of the ObjectList variable to the CSV format using the JavaScript routine explained before;


• encode to the base64 format of JavaScrip output, as explained before;


• download a CSV file; in this case we can use the content of the index variable to generate a different name for each file (Output1.csv, Output2.csv, ...);


• increase by 1 the value of the index variable;


• check if the content of the index variable now is greater than the calculated number of pages. If so, the cycle ends. If not, it continues with the following steps:


• read the next page from the VCF (in the InCycleRead step) and set as usual the content of the ObjectList variable (the index variable is used to identify the number of the page to be read);


• jump at the beginning of the cycle for the next iteration.

And that's all !

When running the application, after clicking the Unload Data button, a set of CSV files will be automatically downloaded to your PC.

You can upload them to the VCF backend of another BTP subaccount, using the approach I described in my previous blog. Or you can merge them into a single CSV file using a simple script.

Now you'll be able to extract data from your VCF backends, for migrating them to another environment, or for manually processing them and reloading to the same backend. Some of the concepts I described in this article can be reused for implementing other kind of applications. The most interesting are:

• how to parse a JavaScript object and to convert it to the CSV format;


• how to use the paging capability provided by the VCF backend (this capability is also available in other integration services);


• how to download a file generated by the app to you PC.

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.