Reading Excel Files in MODLR

If a scheduled export from another system lands as an Excel workbook (.xlsx/.xlsm) rather than a plain CSV, a Process can read it directly using the workbook object, once the file is on the MODLR filesystem via any Flat File Load method.

INFO

Excel reads aren't streamed - the underlying library has to load the whole workbook into memory to support random access to cells, so large workbooks can use a lot of memory. Call workbook.workbookSetMaxByteArraySize(bytes) if you need to raise the file size limit, and close the workbook with book.close() followed by script.garbageCollect() once you're done with it, to release that memory.

Opening a workbook and reading a range

function pre() {
    // This function is called once before the process is executed.
    // Use this to setup prompts.
    script.prompt('File Name', 'fileName', 'uploads/example/data.xlsx');
    script.log('process pre-execution parameters parsed.');
}

function begin() {
    // This function is called once at the start of the process
    script.log('process execution started.');

    if (datasource.fileSize(fileName) === 0) {
        script.abort('File not found: ' + fileName);
        return;
    }

    const book = workbook.open(fileName);

    try {
        book.selectSheet('Sheet1');

        // Read a range as a 2D array - row 1 is treated as headers here, data starts at row 2
        const rows = book.toMatrix('A1:E100', true);
        const headers = rows[0].map(h => h.toLowerCase().replace(/[^a-z0-9]+/g, '_'));

        const batch = datasource.createBatch('Internal Datastore', 'performance_management.example_import', headers);

        for (let i = 1; i < rows.length; i++) {
            if (rows[i][0] == null) break; // stop at the first blank row
            batch.insert(rows[i]);

            if (script.IsCancelled()) {
                return;
            }
        }

        batch.flush();
    } finally {
        book.close();
        script.garbageCollect();
    }
}

function data(record) {
    // This function is called once for each line of data on the second cycle
    // Use this to build dimensions and push data into cubes
}

function end() {
    // This function is called once at the end of the process
    script.log('process execution finished.');
}

toMatrix and getCell (for reading a single cell, e.g. book.getCell(row, column)) come from the underlying XSSFWorkbook (Apache POI) class - see its documentation for the full set of sheet and cell methods beyond what's covered here.

Excel date values

Excel stores dates as a serial number (days since 1899-12-30, with a quirk that treats 1900 as a leap year), not a date string. If a cell's type is numeric but represents a date, convert it before use:

function excelDateToJSDate(excelDate) {
    const MS_PER_DAY = 24 * 60 * 60 * 1000;
    const excelEpoch = new Date('1899-12-30T00:00:00Z');
    return new Date(excelEpoch.getTime() + (excelDate * MS_PER_DAY));
}

Other workbook functions

• workbook.getColumnName(columnNumber) - converts a 1-based column index to its letter reference (1 → A, 28 → AB), instead of writing your own converter.
• workbook.create() - creates a new, empty workbook (for writing/exporting rather than reading).
• workbook.workbookFromQuery(datasource, query, prompts) - builds a workbook directly from a SQL query, useful for generating an Excel export.
• workbook.convertToBase64(wb) / workbook.convertFromBase64(base64) - convert a workbook to/from a base64 string, useful for emailing a workbook or sending it to an API without saving it to the filesystem first.

See the Workbook Functions reference for the complete list.