---
url: /technical/reading-excel-files.md
description: >-
  How to read data out of an Excel workbook (.xlsx/.xlsm) in a Process, for
  example when a scheduled export lands as an Excel file rather than a CSV.
---

# 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](/technical/flat-file-load.html) 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

```js
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](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html) (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:

```js
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)`](/process-functions/workbook-getcolumnname.html) - converts a 1-based column index to its letter reference (1 → `A`, 28 → `AB`), instead of writing your own converter.
* [`workbook.create()`](/process-functions/workbook-create.html) - creates a new, empty workbook (for writing/exporting rather than reading).
* [`workbook.workbookFromQuery(datasource, query, prompts)`](/process-functions/workbook-workbookfromquery.html) - builds a workbook directly from a SQL query, useful for generating an Excel export.
* [`workbook.convertToBase64(wb)`](/process-functions/workbook-converttobase64.html) / [`workbook.convertFromBase64(base64)`](/process-functions/workbook-convertfrombase64.html) - 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](/process-functions/workbook-functions.html) reference for the complete list.