Skip to Content
Technical Articles
Author's profile photo Ferry Djaja

Excel File Upload From SAP Analytics Cloud Analytic Application

There was  a requirement to upload an Excel file directly from the SAC Analytic Application. I would like to share here how I did it.

We will create a custom widget in SAC with library from SheetJS with a little modification. We will also create table, view and stored procedure in HANA. I am using HANA Classic for this purpose.

In a nutshell, we need to create the following modules:

  • HANA Table and Calculation View
  • HANA Stored Procedure
  • HANA XSJS
  • SAC Custom Widget
  • SAC Analytic Application

HANA Table and View

Create HANA table SACXLSX.hdbtable. This table will store the data from Excel file.

// To define an HDB table with main sql type columns, you can use the following code.
// In the example below a schema should exist.   

table.schemaName = "COMMON";
table.tableType = COLUMNSTORE; // ROWSTORE is an alternative value

table.columns = [
    {name = "DATE"; sqlType = DATE;  comment = "date - YYYYMMDD";},
    {name = "COUNTRY_CODE"; sqlType = NVARCHAR; length = 2; comment = "Country Code";},
    {name = "COMPANY_CODE"; sqlType = NVARCHAR; length = 10; comment = "Company Code";},
    {name = "TYPE"; sqlType = NVARCHAR; length = 30; comment = "Type of Data";},
    {name = "VALUE_DATE"; sqlType = DATE; comment = "Value Date - YYYYMMDD";},
    {name = "AMOUNT"; sqlType = DOUBLE; comment = "Amount";},
    {name = "CURRENCY"; sqlType = NVARCHAR; length = 3; comment = "Currency";},
    {name = "COMMENTS"; sqlType = NVARCHAR; length = 100; comment = "Comments";},  
    {name = "LOCK_FLAG"; sqlType = NVARCHAR; length = 10; comment = "Lock Flag";}
];

Create HANA Calculation View TC_SACXLSX.calculationview. We will later create the model in SAC Analytic Application with this view.

HANA Stored Procedure

Create stored procedure insertData.hdbprocedure to insert data to table SACXLSX.

PROCEDURE "insertData" ( 
	in DATE DATE,
	in COUNTRY_CODE NVARCHAR(2),
	in COMPANY_CODE NVARCHAR(10),
	in TYPE NVARCHAR(30),
	in VALUE_DATE DATE,
	in AMOUNT DOUBLE,
	in CURRENCY NVARCHAR(3),
	in COMMENTS NVARCHAR(100),
	in LOCK_FLAG NVARCHAR(1)
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA <default_schema_name>
	--READS SQL DATA AS
	AS -- "READS SQL DATA " removed  
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
insert into
	"SACXLSX"
	values 
		(
			DATE,
			COUNTRY_CODE,
			COMPANY_CODE,
			TYPE,
			VALUE_DATE,
			AMOUNT,
			CURRENCY,
			COMMENTS,
			LOCK_FLAG
		);
END;

HANA XSJS

Create XSJS processData.xsjs. This server-side scripting will process the data from the HTTP Post request from the SAC custom widget and calls the store procedure InsertData to insert the records to table.

try {
	var content = $.request.body.asString();	
	var data = JSON.parse(content);	
	var result = ""; 
	var DATE;
	var COUNTRY_CODE;
	var COMPANY_CODE;
	var TYPE;
	var VALUE_DATE;
	var AMOUNT;
	var CURRENCY;
	var COMMENTS;
	var LOCK_FLAG;	 
	
	var conn = $.hdb.getConnection({ "xssqlcc": "anonuser"}); 
	var procedureCall = conn.loadProcedure("insertData");
	
	for(var i = 0; i <data.length; i++) {
		DATE = data[i].DATE;
		COUNTRY_CODE = data[i].COUNTRY_CODE;
		COMPANY_CODE = data[i].COMPANY_CODE;
		TYPE = data[i].TYPE;
		VALUE_DATE = data[i].VALUE_DATE;
		AMOUNT = data[i].AMOUNT;
		CURRENCY = data[i].CURRENCY;
		COMMENTS = data[i].COMMENTS;
		LOCK_FLAG = data[i].LOCK_FLAG;
		
		procedureCall(DATE, COUNTRY_CODE, COMPANY_CODE, TYPE, VALUE_DATE, AMOUNT, CURRENCY, COMMENTS, LOCK_FLAG);		
	}
	
	conn.commit();
	conn.close();
	
	$.response.headers.set("Access-Control-Allow-Origin", "*");
	$.response.contentType = "application/json";
	$.response.setBody(JSON.stringify(data));
	$.response.returnCode = 200;	
} catch (err) {
	$.response.headers.set("Access-Control-Allow-Origin", "*");
	$.response.contentType = "application/json";
        $.response.setBody(JSON.stringify(err.message));
        $.response.returnCode = 200;
}

SAC Custom Widget

In the custom widget, we will load the external library with the modified code from SheetJS, xslx.js.

onCustomWidgetAfterUpdate(changedProperties) {
  var that = this;

  let xlsxjs = "http://localhost/SAC/sacexcel/xlsx.js";
  async function LoadLibs() {
    try {
      await loadScript(xlsxjs, _shadowRoot);
    } catch (e) {
      console.log(e);
    } finally {
      loadthis(that, changedProperties);
    }
  }
  LoadLibs();
}

We’ll create an XMLView with SAPUI5 SimpleForm and FileUploader.

Using SheetJS library, we parse the data from the uploaded Excel file and perform the validation to make sure the data is valid.

Once the data is validated, we will send it to HANA processData.xsjs with POST method.

We’ll wait until the data is completely processed and once is done, we’ll refresh the SAC model.

function loadthis(that, changedProperties) {
  var that_ = that;

  widgetName = changedProperties.widgetName;
  if (typeof widgetName === "undefined") {
    widgetName = that._export_settings.title.split("|")[0];
  }


  div = document.createElement('div');
  div.slot = "content_" + widgetName;

  if (that._firstConnection === 0) {
    let div0 = document.createElement('div');
    div0.innerHTML = '<?xml version="1.0"?><script id="oView_' + widgetName + '" name="oView_' + widgetName + '" type="sapui5/xmlview"><mvc:View height="100%" xmlns="sap.m" xmlns:u="sap.ui.unified" xmlns:f="sap.ui.layout.form" xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc" controllerName="myView.Template"><f:SimpleForm editable="true"><f:content><Label text="Upload"></Label><VBox><u:FileUploader id="idfileUploader" width="100%" useMultipart="false" sendXHR="true" sameFilenameAllowed="true" buttonText="" fileType="XLSM" placeholder="" style="Emphasized" change="onValidate"></u:FileUploader></VBox></f:content></f:SimpleForm></mvc:View></script>';
    _shadowRoot.appendChild(div0);

    let div1 = document.createElement('div');
    div1.innerHTML = '<?xml version="1.0"?><script id="myXMLFragment_' + widgetName + '" type="sapui5/fragment"><core:FragmentDefinition xmlns="sap.m" xmlns:core="sap.ui.core"><SelectDialog title="Partner Number" class="sapUiPopupWithPadding"  items="{' + widgetName + '>/}" search="_handleValueHelpSearch"  confirm="_handleValueHelpClose"  cancel="_handleValueHelpClose"  multiSelect="true" showClearButton="true" rememberSelections="true"><StandardListItem icon="{' + widgetName + '>ProductPicUrl}" iconDensityAware="false" iconInset="false" title="{' + widgetName + '>partner}" description="{' + widgetName + '>partner}" /></SelectDialog></core:FragmentDefinition></script>';
    _shadowRoot.appendChild(div1);

    let div2 = document.createElement('div');
    div2.innerHTML = '<div id="ui5_content_' + widgetName + '" name="ui5_content_' + widgetName + '"><slot name="content_' + widgetName + '"></slot></div>';
    _shadowRoot.appendChild(div2);

    that_.appendChild(div);

    var mapcanvas_divstr = _shadowRoot.getElementById('oView_' + widgetName);
    var mapcanvas_fragment_divstr = _shadowRoot.getElementById('myXMLFragment_' + widgetName);

    Ar.push({
      'id': widgetName,
      'div': mapcanvas_divstr,
      'divf': mapcanvas_fragment_divstr
    });
  }

  that_._renderExportButton();

  sap.ui.getCore().attachInit(function() {
    "use strict";

    //### Controller ###
    sap.ui.define([
      "jquery.sap.global",
      "sap/ui/core/mvc/Controller",
      "sap/ui/model/json/JSONModel",
      "sap/m/MessageToast",
      "sap/ui/core/library",
      "sap/ui/core/Core",
      'sap/ui/model/Filter',
      'sap/m/library',
      'sap/m/MessageBox',
      'sap/ui/unified/DateRange',
      'sap/ui/core/format/DateFormat',
      'sap/ui/model/BindingMode',
      'sap/ui/core/Fragment',
      'sap/m/Token',
      'sap/ui/model/FilterOperator',
      'sap/ui/model/odata/ODataModel',
      'sap/m/BusyDialog'
    ], function(jQuery, Controller, JSONModel, MessageToast, coreLibrary, Core, Filter, mobileLibrary, MessageBox, DateRange, DateFormat, BindingMode, Fragment, Token, FilterOperator, ODataModel, BusyDialog) {
      "use strict";

      var busyDialog = (busyDialog) ? busyDialog : new BusyDialog({});

      return Controller.extend("myView.Template", {

        onInit: function() {
          console.log(that._export_settings.title);
          console.log("widgetName:" + that.widgetName);

          if (that._firstConnection === 0) {
            that._firstConnection = 1;
          }
        },

        onValidate: function(e) {

          var fU = this.getView().byId("idfileUploader");
          var domRef = fU.getFocusDomRef();
          var file = domRef.files[0];
          var this_ = this;

          var oModel = new JSONModel();
          oModel.setData({
            result_final: null
          });

          var reader = new FileReader();
          reader.onload = async function(e) {
            var strCSV = e.target.result;

            var workbook = XLSX.read(strCSV, {
              type: 'binary'
            });

            var result_final = [];
            var result = [];
            var correctsheet = false;

            workbook.SheetNames.forEach(function(sheetName) {
              if (sheetName === "Sheet1") {
                correctsheet = true;
                var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
                if (csv.length) {
                  result.push(csv);
                }
                result = result.join("[$@~!~@$]")
              }
            });

            if (correctsheet) {
              var lengthfield = result.split("[$@~!~@$]")[0].split("[#@~!~@#]").length;
              console.log("lengthfield: " + lengthfield);

              var total = this_.getView().byId("total");
              var rec_count = 0;

              var len = 0;
              if (lengthfield === 9) {
                for (var i = 1; i < result.split("[$@~!~@$]").length; i++) {
                  if (result.split("[$@~!~@$]")[i].length > 0) {

                    var rec = result.split("[$@~!~@$]")[i].split("[#@~!~@#]");
                    if (rec.length > 0) {
                      len = rec[0].trim().length + rec[1].trim().length + rec[2].trim().length + rec[3].trim().length + rec[4].trim().length + rec[
                        5].trim().length + rec[6].trim().length + rec[7].trim().length + rec[8].trim().length;
                      if (len > 0) {
                        rec_count = rec_count + 1;
                        result_final.push({
                          'ID': i,
                          'DATE': rec[0].trim(),
                          'COUNTRY_CODE': rec[1].trim(),
                          'COMPANY_CODE': rec[2].trim(),
                          'TYPE': rec[3].trim(),
                          'VALUE_DATE': rec[4].trim(),
                          'AMOUNT': rec[5].trim().replace(/[,]/g, ""),
                          'CURRENCY': rec[6].trim(),
                          'COMMENTS': rec[7].trim().replace(/["'\n\r]/g, ""),
                          'LOCK_FLAG': rec[8].trim(),
                        });
                      }
                    }
                  }
                }

                if (result_final.length === 0) {
                  fU.setValue("");
                  MessageToast.show("There is no record to be uploaded");
                } else if (result_final.length >= 2001) {
                  fU.setValue("");
                  MessageToast.show("Maximum records are 2000.");
                } else {
                  // Bind the data to the Table
                  oModel = new JSONModel();
                  oModel.setSizeLimit("5000");
                  oModel.setData({
                    result_final: result_final
                  });

                  var oModel1 = new sap.ui.model.json.JSONModel();
                  oModel1.setData({
                    fname: file.name,
                  });
                  console.log(oModel);

                  var oHeaders = {
                    "Authorization": "Basic XXXXXXX",
                    "Content-Type": "application/x-www-form-urlencoded"
                  }

                  var oModel = new JSONModel();

                  console.log(result_final);
                  oModel.loadData("processData.xsjs", JSON.stringify(result_final), true, 'POST', false, true, oHeaders);

                  oModel.attachRequestCompleted(function() {
                    var result = oModel.getData();
                    console.log(result);

                    _result = result;

                    that._firePropertiesChanged();
                    this.settings = {};
                    this.settings.result = "";

                    that.dispatchEvent(new CustomEvent("onStart", {
                      detail: {
                        settings: this.settings
                      }
                    }));
                  });
                  fU.setValue("");
                }
              } else {
                fU.setValue("");
                MessageToast.show("Please upload the correct file");
              }
            } else {
              console.log("Error: wrong xlsx template");
              MessageToast.show("Please upload the correct file");
            }
          };

          if (typeof file !== 'undefined') {
            reader.readAsBinaryString(file);
          }
        },

        wasteTime: function() {
          busyDialog.open();
        },

        runNext: function() {
          busyDialog.close();
        },

      });
    });

    console.log("widgetName Final:" + widgetName);
    var foundIndex = Ar.findIndex(x => x.id == widgetName);
    var divfinal = Ar[foundIndex].div;
    console.log(divfinal);

    //### THE APP: place the XMLView somewhere into DOM ###
    var oView = sap.ui.xmlview({
      viewContent: jQuery(divfinal).html(),
    });

    oView.placeAt(div);
    if (that_._designMode) {
      oView.byId("idfileUploader").setEnabled(false);
    }
  });
}

SAC Analytic Application

Insert the custom widget Excel_1 and table Table_1 with the model from TC_SACXLSX.calculationview.

In Excel_1 widget onStart() event, add the following code to refresh the model:

Table_1.getDataSource().refreshData();

Demo Video

References

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jef Baeyens
      Jef Baeyens

      Hi Ferry,

      Great Post. Do you think the same requirement is in any way possible for a SAC planning (acquired) model?

      Kr,
      Jef

      Author's profile photo Ferry Djaja
      Ferry Djaja
      Blog Post Author

      Hi Jef Baeyens,

      I haven't try with the SAC planning. But I guess it will work as well.

       

      Regards,

      Ferry

      Author's profile photo Jef Baeyens
      Jef Baeyens

      If you think it will work, please let us know how, because you can't use hana stored procedure on planning models.

      Author's profile photo Ferry Djaja
      Ferry Djaja
      Blog Post Author

      Not using HANA Stored Procedure.

      After getting the result from Excel, parse it and write the data to Planning model with createMembers API, for example.

      var id = 1;
      for (var i=0; i<2; i++) {
      	materialMember = ({id: id, description: 'Phone ' + ConvertUtils.numberToString(id), properties: {Material: 'D' + ConvertUtils.numberToString(id), MaterialColor: 'White', MaterialType: 'Phone', Quantity: '1'}});	
              var result = PlanningModel_1.createMembers("Material",materialMember);
      	id = id + 1;
      }
      
      Author's profile photo Ferry Djaja
      Ferry Djaja
      Blog Post Author

      I created a simple demo to upload an Excel File records to Planning Model.

      https://youtu.be/22OYEFJZwr4

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Great stuff, but we need it to update transaction data, not only master data. Do you think that's possible?

      Author's profile photo Ferry Djaja
      Ferry Djaja
      Blog Post Author

      I guess is not possible at this moment.

      Author's profile photo Kamal Kaushik
      Kamal Kaushik

      Hello Mr. Ferry,

      Good day.

      I aware you upload the post 3 year back, Its Nice one for Master Data upload.

      Have you got chance to work on Transactional data load as well via excel. If yes can you share the detail or blog?

      Its really helpful if you can revert.

       

      Thanks in advance.

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Yes, but we also need it for transaction data, a high nr of records need to be validated & uploaded. Hope that is possible somehow...

      Author's profile photo Tuan Hoang Minh
      Tuan Hoang Minh

      Hi Ferry,

      that was a great work. I would like to ask if you have worked for downloading files from a widget.

      For example, I have a widget (SAC analytics cloud Application) including a chart and a table. Then I created Dropdown or Listbox with options to download images or table (as .cvs file). Could you help me out with that thing?

      The .cvs files are the actual filtered data from the table in the widget. Then different widgets/tables can be downloaded different .cvs data

      Thank you very much!

      Author's profile photo Yalavarthy Priyanka
      Yalavarthy Priyanka

      Hi Ferry,

      While Inserting the Excel Custom Widget I am facing the below issue.

      Please guide us to resolve the issue.Excel%20Custom%20Widget

      Excel Custom Widget

       

       

      Author's profile photo Muthukkumaran Muthukannan
      Muthukkumaran Muthukannan

      Hi Ferry,

      While inserting the Excel Custom Widget facing the below mentioned error, Kindly help us to resolve it.

      Author's profile photo Vinay Kumar
      Vinay Kumar

      Hi All,

      Now, Is it possible to upload the transaction data from excel into SAC using Custom Widget or any other means ?