[[{“value”:”
A few weeks ago I got tired of writing the same Excel-import boilerplate for the third time on a project — parse the sheet, loop the rows, hand-check a bunch of fields, insert whatever survives. Every entity ended up with its own copy-pasted upload handler that only that one screen used. This post walks through a small CAP (Node.js) service I built to fix that: one generic upload action that any entity can reuse, with row-level validation and a proper error report instead of a single “something went wrong” toast.
The full source is a working CAP + Fiori Elements-flavoured UI5 app, and everything below is lifted straight from it — nothing simplified for the blog.
What We’re Building
The idea is simple: a user picks an entity from a dropdown, uploads an .xlsx file, and the backend:
- Parses the workbook and reads the first sheet as JSON rows
- Validates every row against rules defined for that entity
- Inserts only the rows that pass validation
- Returns a structured summary — total rows, success count, error count, and the exact row/field/message for every failure
- Logs the whole attempt (file name, counts, and the error list as JSON) to an UploadLogs table, so there’s an audit trail
The “generic” part is that none of this logic is hard-coded to a single entity. Adding a new importable entity is a matter of adding one object to a rules map — no new controller, no new action, no copy-pasted handler.
Project Structure
Nothing exotic here — a standard CAP layout with one Fiori Elements-generated app on top:
Here is the code of the project structure file:
1. The Data Model
Two entities. Employees is the table we’re importing into. UploadLogs is what gives us the audit trail — every upload attempt, successful or not, leaves a record.
Here is the code of schema.cds file:
namespace demo.excelupload;
entity Employees {
key ID : Integer;
name : String(100);
email : String(100);
salary : Decimal(10,2);
}
entity UploadLogs {
key uploadId : UUID;
fileName : String(200);
entityName : String(100);
totalRows : Integer;
successCount : Integer;
errorCount : Integer;
errors : LargeString; // stored as JSON string
uploadedAt : Timestamp @CDS.on.insert: $now;
}
The errors field is worth calling out: rather than modeling a separate association/composition for row errors, I just store the array as a JSON string in a LargeString. For a log table you read back and display as-is, that’s a lot less ceremony than a child entity, and it keeps the log write to a single INSERT.
2. Exposing It: service.cds
The service exposes both entities as plain projections, plus one bound-free action — uploadExcel — that does the actual work. This is deliberately an action, not an entity CREATE, because a single call needs to return a structured, multi-row result (counts + a list of errors), which doesn’t map cleanly onto a normal OData create response.
Here is the code of service.cds file:
using demo.excelupload as db from ‘../db/schema’;
service ExcelUploadService {
entity Employees as projection on db.Employees;
entity UploadLogs as projection on db.UploadLogs;
action uploadExcel(
entityName : String,
fileName : String,
file : LargeBinary
) returns {
totalRows : Integer;
successCount : Integer;
errorCount : Integer;
errors : array of {
row : Integer;
field : String;
message : String;
};
};
}
entityName tells the handler which target table to insert into and which validation rules to apply. The file itself travels as base64-encoded LargeBinary in the request body — no multipart handling needed, which keeps the UI5 side (below) to a plain fetch() call.
3. The Core Logic: service.js
This is the part that actually makes the thing generic. Everything upload-specific for an entity lives in one small config object:
Here is the code of service.js (validation rules) file:
const VALIDATION_RULES = {
Employees: {
name: { required: true },
email: { required: true, type: ’email’ },
salary: { required: true, type: ‘number’, min: 0 }
}
};
To onboard a new entity for upload, that’s the only entity-specific code you write — a rules object. Everything else (parsing, looping, inserting, logging) is shared.
The generic row validator
validateRow() takes a row and a rules object and doesn’t know or care which entity it’s validating. It checks required fields first, then type-specific rules (email format, numeric, minimum value) — but only if the field actually has a value, so a missing-but-optional field doesn’t also get penalised for “not being a number”:
Here is the code of service.js (validateRow function) file:
function validateRow(row, rules) {
const rowErrors = [];
for (const field in rules) {
const rule = rules[field];
const value = row[field];
if (rule.required && (value === undefined || value === null || value === ”)) {
rowErrors.push({ field, message: `${field} is required` });
continue; // skip further checks on this field if it’s missing
}
if (value !== undefined && value !== null && value !== ”) {
if (rule.type === ’email’ && !/^[^s@]+@[^s@]+.[^s@]+$/.test(value)) {
rowErrors.push({ field, message: `${field} must be a valid email` });
}
if (rule.type === ‘number’ && isNaN(Number(value))) {
rowErrors.push({ field, message: `${field} must be a number` });
}
if (rule.type === ‘number’ && rule.min !== undefined && Number(value) < rule.min) {
rowErrors.push({ field, message: `${field} cannot be less than ${rule.min}` });
}
}
}
return rowErrors;
}
It’s intentionally basic — required, email, number, min. That’s enough to cover most master-data imports, and it’s easy to extend with a max, a regex rule, or a custom() function per field if a real project needs it.
The uploadExcel handler
The handler itself reads top to bottom as a checklist. Worth noting: the entity name coming in from the client is validated against this.entities before it’s used for anything, so you can’t point the action at an arbitrary CDS entity that happens to exist in the model but was never meant to be imported into.
Here is the code of service.js (uploadExcel handler) file:
module.exports = class ExcelUploadService extends cds.ApplicationService {
async init() {
this.on(‘uploadExcel’, async req => {
const { entityName, fileName, file } = req.data;
// 1. Validate the target entity exists in this service
const targetEntity = this.entities[entityName];
if (!targetEntity) {
return req.error(400, `Unknown entity: ${entityName}`);
}
// 2. Look up validation rules for this entity (generic lookup)
const rules = VALIDATION_RULES[entityName];
if (!rules) {
return req.error(400, `No validation rules defined for entity: ${entityName}`);
}
// 3. Ensure we have an actual Buffer before parsing
const fileBuffer = Buffer.isBuffer(file) ? file : Buffer.from(file, ‘base64’);
// 4. Parse the Excel file
const workbook = XLSX.read(fileBuffer, { type: ‘buffer’ });
const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];
const rows = XLSX.utils.sheet_to_json(sheet);
// 5. Validate every row using the generic validator
const validRows = [];
const errors = [];
rows.forEach((row, index) => {
const rowNum = index + 2; // +2 = header row + 1-based index
const rowErrors = validateRow(row, rules);
if (rowErrors.length > 0) {
rowErrors.forEach(e => errors.push({ row: rowNum, field: e.field, message: e.message }));
} else {
validRows.push(row);
}
});
// 6. Insert valid rows only
if (validRows.length > 0) {
await INSERT.into(targetEntity).entries(validRows);
}
// 7. Log the upload attempt
await INSERT.into(‘ExcelUploadService.UploadLogs’).entries({
fileName,
entityName,
totalRows: rows.length,
successCount: validRows.length,
errorCount: errors.length,
errors: JSON.stringify(errors)
});
// 8. Return structured summary
return {
totalRows: rows.length,
successCount: validRows.length,
errorCount: errors.length,
errors
};
});
return super.init();
}
};
A couple of details that matter more than they look:
- Row numbers (rowNum = index + 2) are calculated so the error list matches what the user actually sees in Excel — row 1 is the header, so the first data row is row 2. Reporting index (0-based, no header) would send someone to the wrong row every single time.
- Valid rows are inserted in a single batched INSERT.into(…).entries(validRows) rather than row-by-row — much cheaper than N individual inserts, and it means partial success (some rows in, some rejected) still happens in one round trip to the DB.
- The UploadLogs write happens unconditionally, even when errorCount is 0 or successCount is 0 — every attempt is logged, not just the ones that partially failed.
4. The UI Layer
The Fiori app is a plain custom page (View1), not a List Report — an upload screen doesn’t really benefit from the standard List Report/Object Page scaffolding, so I kept it to a Panel with a FileUploader, an entity Select, and a results table that only appears once there’s something to show.
Here is the code of View1.view.xml file:
<mvc:View
controllerName=”project1.controller.View1″
xmlns=”sap.m”
xmlns:mvc=”sap.ui.core.mvc”
xmlns:core=”sap.ui.core”
xmlns:u=”sap.ui.unified”>
<Page id=”page” title=”Generic Excel Upload Demo”>
<content>
<Panel headerText=”Upload” class=”sapUiResponsiveMargin”>
<content>
<VBox class=”sapUiSmallMargin”>
<Label text=”Target Entity”/>
<Select id=”entitySelect” selectedKey=”Employees” width=”200px”>
<core:Item key=”Employees” text=”Employees”/>
</Select>
<Label text=”Excel File (.xlsx)” class=”sapUiTinyMarginTop”/>
<u:FileUploader
id=”fileUploader”
width=”100%”
placeholder=”Choose a file…”
fileType=”xlsx”
change=”onFileChange”/>
<Button
text=”Upload”
type=”Emphasized”
press=”onUploadPress”
class=”sapUiSmallMarginTop”
id=”uploadBtn”/>
<Text id=”statusText” class=”sapUiTinyMarginTop”/>
</VBox>
</content>
</Panel>
<Panel headerText=”Upload Result” class=”sapUiResponsiveMargin” visible=”{ui>/resultVisible}”>
<content>
<VBox class=”sapUiSmallMargin”>
<HBox class=”sapUiSmallMarginBottom”>
<ObjectStatus title=”Total” text=”{ui>/totalRows}” class=”sapUiSmallMarginEnd”/>
<ObjectStatus title=”Success” text=”{ui>/successCount}” state=”Success” class=”sapUiSmallMarginEnd”/>
<ObjectStatus title=”Errors” text=”{ui>/errorCount}” state=”Error”/>
</HBox>
<Table id=”errorTable” items=”{ui>/errors}”>
<columns>
<Column><Text text=”Row”/></Column>
<Column><Text text=”Field”/></Column>
<Column><Text text=”Message”/></Column>
</columns>
<items>
<ColumnListItem>
<cells>
<Text text=”{ui>row}”/>
<Text text=”{ui>field}”/>
<Text text=”{ui>message}”/>
</cells>
</ColumnListItem>
</items>
</Table>
</VBox>
</content>
</Panel>
</content>
</Page>
</mvc:View>
The controller does three things: read the picked file as base64 (FileReader.readAsDataURL, then strip the data: prefix), POST it to the action, and push the response into a local JSON model that the results Panel is bound to:
Here is the code of View1.controller.js file:
sap.ui.define([
“sap/ui/core/mvc/Controller”,
“sap/ui/model/json/JSONModel”,
“sap/m/MessageToast”
], (Controller, JSONModel, MessageToast) => {
“use strict”;
return Controller.extend(“project1.controller.View1”, {
onInit() {
const oUIModel = new JSONModel({
resultVisible: false,
totalRows: 0,
successCount: 0,
errorCount: 0,
errors: []
});
this.getView().setModel(oUIModel, “ui”);
this._selectedFile = null;
},
onFileChange(oEvent) {
const oFiles = oEvent.getParameter(“files”);
this._selectedFile = oFiles && oFiles.length ? oFiles[0] : null;
},
async onUploadPress() {
const oView = this.getView();
const oUIModel = oView.getModel(“ui”);
const sEntity = oView.byId(“entitySelect”).getSelectedKey();
const oStatusText = oView.byId(“statusText”);
if (!this._selectedFile) {
MessageToast.show(“Please choose a file first.”);
return;
}
oStatusText.setText(“Uploading…”);
oUIModel.setProperty(“/resultVisible”, false);
try {
const sBase64 = await this._fileToBase64(this._selectedFile);
const sServiceUrl = “/odata/v4/excel-upload/”;
const response = await fetch(sServiceUrl + “uploadExcel”, {
method: “POST”,
headers: { “Content-Type”: “application/json” },
body: JSON.stringify({
entityName: sEntity,
fileName: this._selectedFile.name,
file: sBase64
})
});
const data = await response.json();
if (!response.ok) {
MessageToast.show(“Error: ” + (data.error?.message || “Upload failed”));
oStatusText.setText(“”);
return;
}
oUIModel.setData({
resultVisible: true,
totalRows: data.totalRows,
successCount: data.successCount,
errorCount: data.errorCount,
errors: data.errors || []
});
oStatusText.setText(“”);
MessageToast.show(“Upload complete.”);
} catch (err) {
MessageToast.show(“Unexpected error: ” + err.message);
oStatusText.setText(“”);
}
},
_fileToBase64(oFile) {
return new Promise((resolve, reject) => {
const reader = new FileReader();
reader.onload = () => {
const sBase64 = reader.result.split(“,”)[1];
resolve(sBase64);
};
reader.onerror = reject;
reader.readAsDataURL(oFile);
});
}
});
});
I went with a plain fetch() to the action’s OData path rather than wiring this through the OData V4 model’s callFunction/invoke API. For a single action with a binary payload, the model API adds more indirection than it saves — this way the base64 conversion and the request are both fully in my control, and it’s obvious from reading the controller exactly what’s going over the wire.
5. Trying It Out
With the app running (cds watch), an .xlsx with columns name, email, salary and a mix of valid and deliberately broken rows (a blank email, a negative salary, a non-numeric salary) produces exactly what you’d expect: the valid rows land in Employees, and the results panel lists every rejected row with the row number, the offending field, and why it failed — so the person fixing the file doesn’t have to guess.
Every attempt — success or partial failure — also shows up as a new row in UploadLogs, with the error list preserved as JSON. That’s been genuinely useful even outside the demo: when someone asks “did my file from yesterday actually go in?”, the answer is one query away instead of a shrug.
6. Extending to More Entities
Since the whole point was to avoid a one-off handler per entity, adding a second importable entity is three small, additive changes and zero changes to the handler itself:
- Add the entity to db/schema.cds
- Expose it as a projection in service.cds
- Add its field rules to VALIDATION_RULES in service.js
The uploadExcel action, the validateRow() function, the batching, and the logging are all shared as-is. That’s the whole value proposition of building it this way instead of one action per entity.
Conclusion
None of the individual pieces here are complicated — parsing a workbook, looping rows, an INSERT. What actually removes the repeated work is keeping the entity-specific bit (the rules) completely separate from the entity-agnostic bit (parse → validate → insert → log), so the second, third, and tenth entity you need to import cost you a rules object instead of a new handler.
A few things I’d add before calling this production-ready: a max file size / row count check before parsing (a 200MB spreadsheet will parse just fine into a very unhappy Node process), a dry-run mode that validates without inserting, and pulling VALIDATION_RULES out of code and into a config entity so business users could adjust required fields without a redeploy. All reasonable next posts.
If you end up building something similar, or have a cleaner approach to the row-error reporting, I’d genuinely like to hear about it in the comments.
Thank You
“}]]
#abap