Skip to Content
Technical Articles
Author's profile photo Kanika Malhotra

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.

const query = INSERT.into(entity).entries(data);
let srv = awaitcds.connect.to(‘StudentsSrv’);
const Result = await srv.run(query);

 

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

Assigned Tags

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

      Very nice blog ..Thank you

      Author's profile photo Kanika Malhotra
      Kanika Malhotra
      Blog Post Author

      Thanks Amit.

      Author's profile photo Rohit Patil
      Rohit Patil

      hi @Kanika

      can u send me this project git hub link? because I face a number of errors

      Author's profile photo Kanika Malhotra
      Kanika Malhotra
      Blog Post Author

      Hi Rohit

      Can you explain what is the issue you are facing in which part of the code?

      Thanks

      Kanika

      Author's profile photo Kamal Jain
      Kamal Jain

      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.

      Author's profile photo Kanika Malhotra
      Kanika Malhotra
      Blog Post Author

      Thanks Kamal.

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hi Kanika,

      Are there any limitations to the file size or performance implications for uploading very large gigabyte size files?

      Thanks,

      Mustafa.

      Author's profile photo Nitin Saxena
      Nitin Saxena

      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

      Author's profile photo Sanjay Aswani
      Sanjay Aswani

      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

      Author's profile photo Sanjay Aswani
      Sanjay Aswani

      It was related to csrf error. After adding headers with x-csrf-token in file uploader the network call is working fine

      Author's profile photo Chan Chiew Voon
      Chan Chiew Voon

      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

      Author's profile photo Mikhail Ponomarev
      Mikhail Ponomarev

      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.

      Author's profile photo Rohit Patil
      Rohit Patil

      hi Kanika

       

      I have followed all the steps mentioned in the blog post but I got error