How to parse CSV data in Oracle Visual Builder using JS library

How to parse CSV data in Oracle Visual Builder using JS library

Do not reinvent the wheel by writing complex JS.

In this blog we will see how to use a library in Visual Builder to parse CSV data.
The library we are using here is papaparse.

Background

We have come across many scenarios where there is a need to parse the CSV data. For example but not limited to ,

  • CSV file uploaded (through file picker) which needs to be converted to JSON for further processing.
  • BIP response (obtained like this) which needs to be displayed on screen.

A simpler/noob way to do this was using String.prototype.split() to split data based on a delimiter like comma (,) but then this is not a foolproof way to do it.
It fails where the data contains a comma enclosed within a double quote.
Hence instead of writing our custom code we can make use of existing libraries.
We will build a simple app which accepts a csv file and parses it into a JSON.

Import the library in Visual Builder

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. Either way the steps would be similar to that mentioned here

Configure requirejs paths

In the Application level, open the JSON file and add a new entry for the library as shown.

RequireJS.png

  "requirejs": {
    "paths": {
      "papa": "https://unpkg.com/papaparse@5.3.2/papaparse.min"
    }
  },

OR (if the library is downloaded and placed under resources/js)

 "requirejs": {
    "paths": {
      "papa": "resources/js/papaparse.min"
    }
  },

Use it in the application

So far, we have only configured from where we need to load our library. Now we will load and use it. In our page module define function, we need to make an entry for the library

define(['papa'], (Papa) => { ... })

If we need to parse a CSV which is present as string then it is as simple as , Papa.parse('a,c,b')

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

ParseData(file) {
    return new Promise(function(resolve, reject) {
        Papa.parse(file, {
            complete: function(results) {
                console.log(results.data);
                resolve(results.data);
            },
            header: true
        });
    });
}

We have used header: true to specify that first row is header.
Likewise there are many configurations as mentioned here.

Next, we just have to invoke this function from our action chain by passing in the File Picker's File and consume the response of this function.

Image.PNG

The complete application code is GIT here. Just download the ZIP and import it.

Credits : Soomedh for article on using external libraries

Edit 1 : If we need to parse the base64 encoded CSV results of a BIP WebService response then the following snippet can be used. Here reportOutput is the reportBytes of the Web Service response.

_FormatReport(reportOutput) {
      var Output = decodeURIComponent(window.escape(atob(reportOutput)));
      var response = Papa.parse(Output.trim(), { header: true });
      return response.data;
    };