Technical Articles
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();
Hi Ferry,
Great Post. Do you think the same requirement is in any way possible for a SAC planning (acquired) model?
Kr,
Jef
Hi Jef Baeyens,
I haven't try with the SAC planning. But I guess it will work as well.
Regards,
Ferry
If you think it will work, please let us know how, because you can't use hana stored procedure on planning models.
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.
I created a simple demo to upload an Excel File records to Planning Model.
https://youtu.be/22OYEFJZwr4
Great stuff, but we need it to update transaction data, not only master data. Do you think that's possible?
I guess is not possible at this moment.
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.
Yes, but we also need it for transaction data, a high nr of records need to be validated & uploaded. Hope that is possible somehow...
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!
Hi Ferry,
While Inserting the Excel Custom Widget I am facing the below issue.
Please guide us to resolve the issue.
Excel Custom Widget
Hi Ferry,
While inserting the Excel Custom Widget facing the below mentioned error, Kindly help us to resolve it.
Hi All,
Now, Is it possible to upload the transaction data from excel into SAC using Custom Widget or any other means ?