Technical Articles
Populate Master Data in SAC Planning Model from an Excel File Upload with Analytic Application
In this short tutorial, we will create an SAC Analytic Application (custom widget) Excel File Upload to populate the master data in SAC planning model.
We will create the following modules:
- Planning Model
- Excel File Upload Custom Widget
- Analytic Application
Create a Planning Model
Excel File Upload Custom Widget
Create a custom widget with UI5 component an XMLView with SAPUI5 SimpleForm and FileUploader. Here is the main code in loadthis() function. The output of this function is just simply the records from Excel file in ‘stringify’ JSON format.
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);
_result = JSON.stringify(result_final);
that._firePropertiesChanged();
this.settings = {};
this.settings.result = "";
that.dispatchEvent(new CustomEvent("onStart", {
detail: {
settings: this.settings
}
}));
this_.runNext();
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
Create a canvas and insert the table Table_1, custom widget Excel_1 and text Text_1.
Create script variable InventoryPlanning with type PlanningModelMember.
Insert the planning model we created earlier.
On the onStart() event of custom widget Excel_1, insert the script to parse the data and call the API PlanningModel.createMembers() to submit the data to the planning model. Take a note that the dimension type Account is not supported with this API calls.
I am not sure if there is a simple function to parse the JSON data like in JavaScript JSON.parse(). Since I couldn’t find any, I wrote this kind of long codes to achieve the same thing. If you have any better ideas, do let me know in the comment.
var Inventory = PlanningModel_1.getMembers("Inventory");
console.log(Inventory.length);
var last_id = 0;
if(Inventory.length > 1) {
last_id = Inventory.length;
}
console.log("last id:");
console.log(last_id);
var str = Excel_1.getUnit();
var str1 = str.split("},{");
var entity = "";
var ID = "";
var DATE = "";
var COUNTRY_CODE = "";
var COMPANY_CODE = "";
var TYPE = "";
var VALUE_DATE = "";
var AMOUNT = "";
var CURRENCY = "";
var COMMENTS = "";
var LOCK_FLAG = "";
var Ar_ID = ArrayUtils.create(Type.string);
var Ar_DATE = ArrayUtils.create(Type.string);
var Ar_COUNTRY_CODE = ArrayUtils.create(Type.string);
var Ar_COMPANY_CODE = ArrayUtils.create(Type.string);
var Ar_TYPE = ArrayUtils.create(Type.string);
var Ar_VALUE_DATE = ArrayUtils.create(Type.string);
var Ar_AMOUNT = ArrayUtils.create(Type.string);
var Ar_CURRENCY = ArrayUtils.create(Type.string);
var Ar_COMMENTS = ArrayUtils.create(Type.string);
var Ar_LOCK_FLAG = ArrayUtils.create(Type.string);
for(var x=0; x<str1.length; x++) {
var str2 = str1[x].split(",");
for(var y = 0; y<str2.length; y++) {
entity = str2[y].replace("[{", "");
entity = str2[y].replace("}]", "");
//ID
if(y === 0) {
ID = entity.split(":")[1];
Ar_ID.push(ID);
}
//DATE
if(y === 1) {
DATE = entity.split(":")[1];
DATE = DATE.replace('"','');
DATE = DATE.replace('"','');
Ar_DATE.push(DATE);
}
//COUNTRY_CODE
if(y === 2) {
COUNTRY_CODE = entity.split(":")[1];
COUNTRY_CODE = COUNTRY_CODE.replace('"','');
COUNTRY_CODE = COUNTRY_CODE.replace('"','');
Ar_COUNTRY_CODE.push(COUNTRY_CODE);
}
//COMPANY_CODE
if(y === 3) {
COMPANY_CODE = entity.split(":")[1];
COMPANY_CODE = COMPANY_CODE.replace('"','');
COMPANY_CODE = COMPANY_CODE.replace('"','');
Ar_COMPANY_CODE.push(COMPANY_CODE);
}
//TYPE
if(y === 4) {
TYPE = entity.split(":")[1];
TYPE = TYPE.replace('"','');
TYPE = TYPE.replace('"','');
Ar_TYPE.push(TYPE);
}
//VALUE_DATE
if(y === 5) {
VALUE_DATE = entity.split(":")[1];
VALUE_DATE = VALUE_DATE.replace('"','');
VALUE_DATE = VALUE_DATE.replace('"','');
Ar_VALUE_DATE.push(VALUE_DATE);
}
//AMOUNT
if(y === 6) {
AMOUNT = entity.split(":")[1];
AMOUNT = AMOUNT.replace('"','');
AMOUNT = AMOUNT.replace('"','');
Ar_AMOUNT.push(AMOUNT);
}
//CURRENCY
if(y === 7) {
CURRENCY = entity.split(":")[1];
CURRENCY = CURRENCY.replace('"','');
CURRENCY = CURRENCY.replace('"','');
Ar_CURRENCY.push(CURRENCY);
}
//COMMENTS
if(y === 8) {
COMMENTS = entity.split(":")[1];
COMMENTS = COMMENTS.replace('"','');
COMMENTS = COMMENTS.replace('"','');
Ar_COMMENTS.push(COMMENTS);
}
//LOCK_FLAG
if(y === 9) {
LOCK_FLAG = entity.split(":")[1];
LOCK_FLAG = LOCK_FLAG.replace('"','');
LOCK_FLAG = LOCK_FLAG.replace('"','');
Ar_LOCK_FLAG.push(LOCK_FLAG);
}
}
}
console.log(Ar_ID);
console.log(Ar_DATE);
console.log(Ar_COUNTRY_CODE);
console.log(Ar_COMPANY_CODE);
console.log(Ar_TYPE);
console.log(Ar_VALUE_DATE);
console.log(Ar_AMOUNT);
console.log(Ar_CURRENCY);
console.log(Ar_COMMENTS);
console.log(Ar_LOCK_FLAG);
Application.showBusyIndicator();
var idx = last_id;
for(var i=0; i<Ar_ID.length; i++) {
idx = idx + 1;
InventoryPlanning = ({id: idx, description: '', properties: {Amount: Ar_AMOUNT[i], Comment: Ar_COMMENTS[i], Company_Code: Ar_COMPANY_CODE[i], Country_Code: Ar_COUNTRY_CODE[i], Currency_Info: Ar_CURRENCY[i], Date: Ar_DATE[i], Lock_Flag: Ar_LOCK_FLAG[i], Type: Ar_TYPE[i], Value_Date: Ar_VALUE_DATE[i]}});
var result = PlanningModel_1.createMembers("Inventory",InventoryPlanning);
console.log(result);
}
Application.hideBusyIndicator();
Table_1.getDataSource().refreshData();
And that’s it.
Demo Video
References
-
Maintain Master Data in Planning Model – Sap Analytics Cloud (SAC)
-
Excel File Upload From SAP Analytics Cloud Analytic Application
- Build a Custom Widget in SAP Analytics Cloud, Analytics Application
Hi Ferry,
Can you share the JSON file for this as well? As I understand, the code that was posted above is for main js.
Thanks,
Francis
Where did you able to create a json document?
Hi Ferry,
Could you share the JSON file ?
Thanks,
Varun
Hi Ferry,
I am trying to upload the master data as a flat file, but I am getting the below error
could please help us in resolving the below error
Console Error
Network preview
Thanks
Harish
Harish Baire
Are you able to create the custom widget? If yes, please share the steps.
Thank You
Ferry Djaja,
Can you provide the JSON file to create the Excel_1 custom widget?