Rob's SAPUI5 Tips: How To Upload Data from Excel (csv file) into SAP via SAPUI5 Application

Background:

We recently made a custom SAPUI5 application for one our clients which allows their remote customers to capture certain data into their on-premise SAP CRM system for specialized statutory reporting. The application was designed to have the input fields logically grouped in different XML views and based on the sheer volume of data they required to be captured the number of pages easily grew. As such, the data capture process has become quite cumbersome and error-prone considering the fact that the back-end itself has grown over time to have hundreds of fields to accommodate this data.

In this article I offer an alternative design which makes changes only to the UI5 front-end and leaves the back-end untouched. This way we can take advantage of the natural decoupling offered by SAP between the UI5 front-end and it's corresponding OData backend services. The goal here is to facilitate as little disruption as possible to the already working OData components by only changing the UI5 frontend.

The Excel Template (to be saved as a csv file):

The client indicated that upon carrying out a survey their customers indicated that they are comfortable using Excel to capture the required data and saving it as a csv file . Based on this information the application therefore needs to simply enable them to upload this csv file client-side and post to the already existing OData service. For this purpose a standardized data upload Excel template can be used (sample below). This can have whatever fields you require according to your own scenario/ use case. Our scenario has hundreds but to keep this article simple I've used just two fields:

Note: I will not go into the specifics of setting up a SAPUI5 Application in this post but will focus on the mechanics of the actual uploading solution.

The Application -> View (View1.view.xml):

My view will be a basic one with the following main controls:

• FileUploader (sap.ui.unified): for selecting the csv file with the data to be uploaded

• "Import" Button (sap.m): for triggering the import of the csv data into the App (press event)

• Table (sap.m): for displaying the data imported into the App (for verification before submitting to the OData service)

NB: Proper internationalization dictates that texts be placed in the i18n file for translation but for simplicity I've hardcoded them in the view itself.            

• "Submit" Button (sap.m): for submitting the data to the OData service for uploading into the backend CRM system

The Application -> Controller (View1.controller.js):

For details on the implementation specifics for the methods of the fileuploader you can consult the SAPUI5 Demo Kit here: https://sapui5.hana.ondemand.com/test-resources/sap/ui/commons/demokit/FileUploader.html#__2.

Here I will detail the "press" event of the "Import" button which will import the data from the csv file and populate the "itemsTable" table for viewing. Next I will detail the "press" event of the "Submit" button which takes the data from the Table, maps it into an object which is then forwarded to the CREATE_ENTITY method of the OData service.

• "Import" Button press event:

The onUploadPress function above creates a FileReader object and attaches a function to its "onload" event. This is the function in which we perform the task of reading the csv file and extracting the data lines one by one. After extracting a row we traverse each of its columns getting column values and putting them into an object "obj". When the for loop exits obj will have all the data for a single row and we then push this to an array "data". By so doing when we exit the while loop we would have extracted all the values for all the rows in the csv file and pushed them to the array "data". The next step is to then set the array as the data for a JSONModel and setting that JSONModel as the model for the Table. The reader's readAsBinaryString method then triggers the afore-mentioned function on the onload event.  

• Submit Button press event:

Finally this onSubmit function makes use of a helper function "mapoEntry" to, yes you guessed it, map the Table's data to an object "oEntry" which will be the medium through which the data passed to the backend OData service when the respective EntitySet is called to trigger its CREATE_ENTITY method. Here the implementation will be increasingly use-case specific so I've just used a rather simplistic implementation to show the major components of such a solution. Once extracted into the SAPUI5 App, the data can be easily forwarded to a back-end OData service. Shown below is what the above sample looks like:  

I hope the components laid out in this article can assist you in designing your own implementation.