How to convert Excel to CSV or JSON in VBCS

We  usually get requirement where  user is uploading an excel file in our custom VBCS application and submitting an OIC integration.

Currently OIC doesn't support reading of an excel file. So after the user uploads a file in VBCS, we need to convert in csv,json or xml to read easily in OIC.

There are multiple ways to achieve this, two of the common ones are:

1). Using xlsx external JS library in VBCS

      In this we need to import external library and write custom JS function to convert from excel to csv or json

2). Using OCI functions

     In this we can write our function in java, python, ruby etc.

    Below are some of the pre-requisites:

     a). OCI CLI to be installed locally

     b). Docker desktop

     c). Policies to the user to create and manage functions

     d). OCR - Oracle container registry

     e).  fn CLI to be installed

     f). IDCS or OCI local user having capabilities of generating Auth tokens.

    We will see the setup and uses of OCI function in detail in a separate post as it has lot of steps.

In this post we will see how we can achieve this directly in VBCS.

We will create a simple page and have a file picker, a submit button and a text area to show the converted file.

 

We will declare variable like below:

Now in the file picker action chain we can perform below:

We will reset the FileUpload var.

Also we will  assign value to FileUpload variable fields since it is object type.

 

 

 

 

 

Now before going to action chain of  Submit button let us see the import of external xlsx library.

Since we need to use a third-party library, we need to import it first.
It can be either a CDN based import, or we can place it in our resources/js folder. 

We can refer below link to see details on how to import library in VBCS.

How to import external libraries in Visual Builder

In our example we are using CDN based, which will look like below.

Go to app-flow.json and make an entry like below

"requirejs":{
    "paths":{
     "xlsx":"https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.2/xlsx.full.min"    }
  }

 

 Now we will see how we can use it in our application:

Creation action chain for Submit button. in the action chain we will call custom JS to convert excel to csv.

Also we will assign the converted value to text area variable.

 

 

This function will take file as input and will return the  converted to csv data.

 

 

Now let us see what all we need to do inside function. Click on Go to Module function.

Here we will load and use it. In our page module define function, we need to make an entry for the library

define(['xlsx'], function(XLSX) 

 

As we need to parse a file then we need to use of JS promise since its asynchronous.

Below is the function that we need to write:

PageModule.prototype.ParseExcelData = function (file) {

return new Promise(function(resolve, reject) {

let fileReader = new FileReader();

fileReader.readAsBinaryString(file);

fileReader.onload = (event)=>{

let data = event.target.result;

let workbook = XLSX.read(data,{type:"binary"});

workbook.SheetNames.forEach(sheet => {

if(sheet == 'Sheet1'){

let rowObject = XLSX.utils.sheet_to_csv(workbook.Sheets[sheet]);

console.log(rowObject);

resolve(rowObject);

}

});

}

});

}; 

 

Below is the screenshot of complete code.

 

 

After this we will assign the JS response to text area variable.

Now we are good to test.

Run the preview.

Keep the google chrome developer console opened, we can see the external js load and

other things related to our application .

We can see in the above screenshot that the xlsx library got download as the page loaded

Now let us upload and excel file and test.

Below is the sample excel file we will be using.

Note: The sheet name is "Sheet1" , we have hard coded this in our JS function.

In the below screenshot we can see that it has successfully converted the excel file to csv.

We can go to console tab in the developer console and enable verbose as well in levels to see

the processing in detail:

If we want to convert the excel to json then we can use sheet_to_json instead of sheet_to_csv

in the JS function.

When assigning the response of JS to textarea variable we can use strigify like below:

JSON.stringify($chain.results.callFunctionParseExcelData,null,2)