Upload data from excel in CAP (Node.js)

[KM11]

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

[AmitSAP11]

Very nice blog ..Thank you

[KM11]

Thanks Amit.

[Rohit_Patil]

hi @kanikamalhotra

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

[KM11]

Hi Rohit

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

Thanks

Kanika

[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.

[KM11]

Thanks Kamal.

[MustafaBensan]

Hi kanikamalhotra,

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

Thanks,

Mustafa.

[former_member740777]

Hi kanikamalhotra 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

[aswani_sanjay]

Hi kanikamalhotra

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

[aswani_sanjay]

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

[BrianChan]

Hi kanikamalhotra

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

[epamtiosteel]

Hi kanikamalhotra,

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.

[Rohit_Patil]

hi kanikamalhotra

 

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

[Pallav]

How are you sending the x-csrf-token in file uploader ?

[Attila]

Hi All,

I just want to give two small little hints to this great blog post, after putting it into action within an OData V4 Fiori Elements application.

Point1 - Fetching tokens with oData v4 and managing slugs/header parameters

removeHeaderParameter and addHeaderParameter works by id not name (at least this is valid for UI5 108 and above, not tested with older versions). If You want a token and apply it for Put-ing data, You can do like this in the controller when starting the upload:

 let oFileUploader = byId("idFileUploader");

                    oFileUploader.removeHeaderParameter('slug');

                    oFileUploader.removeHeaderParameter('X-CSRF-Token');



                    oFileUploader.addHeaderParameter(new sap.ui.unified.FileUploaderParameter("slug", { name: "slug", value: Entity }));

                    oFileUploader.addHeaderParameter(new sap.ui.unified.FileUploaderParameter("X-CSRF-Token", {

                        name: "X-CSRF-Token", value: oExtensionAPI.getModel().getHttpHeaders()["X-CSRF-Token"]

                    }));

Point2 - security vulnerability of the XLSX library

This library is not maintained anymore on npmjs.com, and this last obsolete version contains a high security risks.

Download the latest stable and secure version from sheetjs.com and place it in a folder like vendor in the project root. Then add this as dependency in package.json. This is one of the alternatives sourcing packages not from npmjs.com.

"xlsx": "file:vendor/xlsx-0.20.0.tgz"

Best regards

Attila

[itsmepankaj]

can i get a explanation why i am getting this type of error when i exceute this code??

[KM11]

We tried with max10k records and it worked fine. It also depends with the amount of validations the data is going through before getting into the table.

If we have more data load we can call a db procedure to insert the data instead of db.run().

[KM11]

We tried with max10k records and it worked fine. It also depends with the amount of validations the data is going through before getting into the table.

If we have more data load we can call a db procedure to insert the data instead of db.run().

[KM11]

Hi Brian

 

Along with req.notify/req.info/req.warn/req.reply you can append your own message. It gets displayed successfully.

 

Thanks

Kanika

[KM11]

Hi Rohit

Please check your URL in file uploader. In local it shall start with a '/' .

Thanks

Kanika

[KM11]

Hi Attila

Definitely some valuable inputs.

Thanks

Kanika

[KM11]

Hi Pankaj

 

Please check the error in your console. Looks like some authorization error, showing forbidden on the screen. Check your roles or scopes if any in the xs-security .json

Thanks

Kanika

[former_member182874]

Hi Kanika,

kanikamalhotra 

Thanks for sharing this. I am trying the same in my CAP Fiori program. However there is some path mismatch. On click of the button there is error reading the Upload.js file.

Will be great if you can share your folder paths or any github repo link for reference.

Regards,

Tejas

[former_member563581]

Hi Attila, @attila.berencsi

Nice !

Do you have a git repository path for your project which I can refer to 🙂 ?

Regards,

Tejas

[Attila]

Hi Tejas,

no, I do not have such, due it is corporate intellectual property. I applied the adjustments to the frontend code explained here referenced from repository

https://github.com/SAP-samples/cloud-cap-samples-java/blob/main/app/admin/webapp/extension/Upload.js

BR, Attial

[former_member563581]

Yep I have been referring the same. Although I was interested in the file path for xlsx

"xlsx": "file:vendor/xlsx-0.20.0.tgz"

Thanks

[ajithsrkmr93]

How did you resolve this issue>>??

[ajithsrkmr93]

i am getting the same issue. and i put '/' also. Could you please share the github repo for this

[sagar17bansal]

Hi Kanika,

Thanks for this wonderful blog.

I was wondering if this solution will support mass Delete/Update operations as well?
Please let me know if you have already tried any of these.

Also, will it be possible for you to share the github link for this project?

Thanks,
Sagar Bansal