Technical Articles
Upload data from excel in CAP (Node.js)
This example demonstrates how to upload data from excel into HANA cloud tables. This can be beneficial for mass upload of data using excel.
A fiori elements list report is created to display and upload the data into the tables. Upload button is added as an extension to the List Report Application.
The upload entity in this can be re-used for updating data in multiple tables/entities.
The uploaded data is parsed and inserted into respective entity. We can write validations before upload by internally invoking the custom handlers of the respective entity using srv.run.
CAP DB and SRV Artifacts :
Database :
entity Students : cuid {
StudentId: String(6);
FirstName: String;
LastName: String;
DOB: Date;
Address: String;
}
Service:
service StudentsSrv {
@cds.persistence.skip
@odata.singleton
entity ExcelUpload {
@Core.MediaType : 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
excel : LargeBinary;
};
entity Students as projection on db.Students
}
Service.js: ( Custom Handlers)
srv.on('PUT', “ExcelUpload", async (req, next) => {
if (req.data.excel) {
var entity = req.headers.slug;
const stream = new PassThrough();
var buffers = [];
req.data.excel.pipe(stream);
await new Promise((resolve, reject) => {
stream.on('data', dataChunk => {
buffers.push(dataChunk);
});
stream.on('end', async () => {
var buffer = Buffer.concat(buffers);
var workbook = XLSX.read(buffer, { type: "buffer", cellText: true, cellDates: true, dateNF: 'dd"."mm"."yyyy', cellNF: true, rawNumbers: false });
let data = []
const sheets = workbook.SheetNames
for (let i = 0; i < sheets.length; i++) {
const temp = XLSX.utils.sheet_to_json(
workbook.Sheets[workbook.SheetNames[i]], { cellText: true, cellDates: true, dateNF: 'dd"."mm"."yyyy', rawNumbers: false })
temp.forEach((res, index) => {
if (index === 0) return;
data.push(JSON.parse(JSON.stringify(res)))
})
}
if (data) {
const responseCall = await CallEntity(entity, data);
if (responseCall == -1)
reject(req.error(400, JSON.stringify(data)));
else {
resolve(req.notify({
message: 'Upload Successful',
status: 200
}));
}
}
});
});
} else {
return next();
}
});
srv.before('POST', 'Students', async (req) => {
//Custom validations can be put, if required before upload
};
srv.on('POST', 'Students', async (req) => {
//return reponse to excel upload entity .
});
async function CallEntity(entity, data) {
if (entity === Students) {
//If any custom handling required for a particular entity
}
const insertQuery = INSERT.into(entity).entries(data);
// This calls the service handler of respective entity. It can be used if any custom
validations need to be performed. or else custom handlers can be skipped.
let srv = await cds.connect.to('StudentsSrv');
const insertResult = await srv.run(insertQuery);
let query = SELECT.from(entity);
await srv.run(query);
return insertResult; //returns response to excel upload entity
};
CAP APP Artifacts: Create a list report application using fiori template and extend the application to add custom upload button on the list page.
Manifest.json
"controlConfiguration": {
"@com.sap.vocabularies.UI.v1.LineItem": {
"actions": {
"Upload": {
"id": "UploadButton",
"text": "Upload",
"press": "com.test.ExtensionController.Upload",
"requiresSelection": false
},
ExcelUploadDialog.fragment.xml:
<core:FragmentDefinition xmlns:core="sap.ui.core"
xmlns:u="sap.ui.unified"
xmlns="sap.m">
<Dialog id="_IDDialog"
title=“Excel Upload"
class="sapUiResponsiveContentPadding"
beforeOpen=".onBeforeOpen"
afterClose=".onAfterClose">
<content>
<u:FileUploader id="uploader"
fileType="xlsx"
multiple="false"
uploadUrl=“StudentsSrv/ExcelUpload/excel"
fileAllowed=".onFileAllowed"
fileEmpty=".onFileEmpty"
uploadComplete=".onUploadComplete"
typeMissmatch=".onTypeMismatch"
sendXHR="true"
useMultipart="false"
placeholder="Choose a xlsx file..."
httpRequestMethod="Put" />
</content>
<beginButton>
<Button id="ok"
text="OK"
press=".onOk"
type="Emphasized"
enabled="false" />
</beginButton>
<endButton>
<Button id="cancel"
text="Cancel"
press=".onCancel" />
</endButton>
</Dialog>
</core:FragmentDefinition>
ExtensionController:
sap.ui.define(["sap/m/MessageBox", "sap/m/MessageToast", "sap/ui/core/UIComponent"],
function (MessageBox, MessageToast, UIComponent) {
"use strict";
function _createUploadController(oExtensionAPI, Entity) {
var oUploadDialog;
function setOkButtonEnabled(bOk) {
oUploadDialog && oUploadDialog.getBeginButton().setEnabled(bOk);
}
function setDialogBusy(bBusy) {
oUploadDialog.setBusy(bBusy)
}
function closeDialog() {
oUploadDialog && oUploadDialog.close()
}
function showError(code, target, sMessage) {
MessageBox.error("Upload failed", {title: "Error"}
}
function byId(sId) {
return sap.ui.core.Fragment.byId("excelUploadDialog", sId);
}
return {
onBeforeOpen: function (oEvent) {
oUploadDialog = oEvent.getSource();
oExtensionAPI.addDependent(oUploadDialog);
},
onAfterClose: function (oEvent) {
oExtensionAPI.removeDependent(oUploadDialog);
oUploadDialog.destroy();
oUploadDialog = undefined;
},
onOk: function (oEvent) {
setDialogBusy(true)
var oFileUploader = byId("uploader");
var headPar = new sap.ui.unified.FileUploaderParameter();
headPar.setName('slug');
headPar.setValue(Entity);
oFileUploader.removeHeaderParameter('slug');
oFileUploader.addHeaderParameter(headPar);
var sUploadUri = oExtensionAPI._controller.extensionAPI._controller._oAppComponent.getManifestObject().resolveUri(“./StudentsSrv/ExcelUpload/excel")
oFileUploader.setUploadUrl(sUploadUri);
oFileUploader
.checkFileReadable()
.then(function () {
oFileUploader.upload();
})
.catch(function (error) {
showError("The file cannot be read.");
setDialogBusy(false)
})
},
onCancel: function (oEvent) {
closeDialog();
},
onTypeMismatch: function (oEvent) {
var sSupportedFileTypes = oEvent
.getSource()
.getFileType()
.map(function (sFileType) {
return "*." + sFileType;
})
.join(", ");
showError(
"The file type *." +
oEvent.getParameter("fileType") +
" is not supported. Choose one of the following types: " +
sSupportedFileTypes
);
},
onFileAllowed: function (oEvent) {
setOkButtonEnabled(true)
},
onFileEmpty: function (oEvent) {
setOkButtonEnabled(false)
},
onUploadComplete: function (oEvent) {
var iStatus = oEvent.getParameter("status");
var oFileUploader = oEvent.getSource()
oFileUploader.clear();
setOkButtonEnabled(false)
setDialogBusy(false)
if (iStatus >= 400) {
var oRawResponse;
try {
oRawResponse = JSON.parse(oEvent.getParameter("responseRaw"));
} catch (e) {
oRawResponse = oEvent.getParameter("responseRaw");
}
if (oRawResponse && oRawResponse.error && oRawResponse.error.message) {
showError(oRawResponse.error.code, oRawResponse.error.target, oRawResponse && oRawResponse.error && oRawResponse.error.message);
}
} else {
MessageToast.show("File uploaded successfully");
oExtensionAPI.refresh()
closeDialog();
}
}
};
};
return {
Upload: function (oBindingContext, aSelectedContexts) {
this.loadFragment({
id: “excelUploadDialog",
name: "com.test.ExcelUploadDialog",
controller: _createUploadController(this, ‘Students’)
}).then(function (oDialog) {
oDialog.open();
});
}
};
});
Summary:
This blog contains code snippets useful for uploading data into tables from excel. We have used a single upload entity which can be re-used for upload of data into various entities. The Upload custom handler parses the uploaded excel using File Uploader and helps to insert that data into respective entity.
References:
https://github.com/SAP-samples/cloud-cap-samples-java/blob/main/app/admin/webapp/extension/Upload.js
Hope this was useful. Feedbacks are appreciated.
Thanks
Kanika
Very nice blog ..Thank you
Thanks Amit.
hi @Kanika
can u send me this project git hub link? because I face a number of errors
Hi Rohit
Can you explain what is the issue you are facing in which part of the code?
Thanks
Kanika
Great Blog Kanika, we have this requirement in one project to upload EXLS file on FTP Server from CAP Model. Will get this use as much as possible.
Thanks Kamal.
Hi Kanika,
Are there any limitations to the file size or performance implications for uploading very large gigabyte size files?
Thanks,
Mustafa.
Hi Kanika will it work for mass upload lets say 15k or 50 k records ?
Right now we are doing excel upload from CAPM but its only getting successful if the records are less then 9000 else i am getting communication error
Hi Kanika
I have followed all the steps mentioned in the blog post and the service call works for the first time. But after the upload is successful and if I am trying to reupload something, the call to handler is not happening and the network call is resulting in error 403.
Did face any such issues or could you please help in resolving the same.
Thanks,
Sanjay
It was related to csrf error. After adding headers with x-csrf-token in file uploader the network call is working fine
Hi Kanika
First of all thanks for the great blog.
I have followed all the steps and managed to get my upload running all right.
But I noticed that with success return, the rawResponse will always be status 204. My requirements need to return different status code based on couple of criteria.
I have tried different return (req.notify/req.info/req.warn/req.reply) and its always 204 on the frontend.
How would you suggest I approach this?
Thanks,
Brian
Hi Kanika,
great article as there's literally no other CAP samples related to that.
However, it's still unclear if it's possible and how to work with any kind of multipart/form-data (e.g with useMultipart="true" on a frontend).
I believe it might be a very helpful Part 2 of the Article.
Thank you.
hi Kanika
I have followed all the steps mentioned in the blog post but I got error