---
url: /technical/flat-file-load.md
description: >-
  How to set up a Flat File Load integration, for platforms that exchange data
  as files rather than through an API.
---

# Flat File Load

Some platforms don't expose an API at all - either a [Standard](/technical/integrations.html#standard-integrations) or [Advanced](/technical/integrations.html#advanced-integrations) Integration isn't possible - and instead exchange data as files (typically CSV). MODLR supports this pattern as a **Flat File Load** integration: a file lands on the MODLR instance's filesystem, and a [Process](/technical/creating-processes.html) reads it in.

There are four ways to get the file onto the instance:

## Option 1: Pull files from an external SFTP server

If the source system exposes its own SFTP server, a Process can connect out to it directly using [`ftp.Connect`](/process-functions/ftp-connect.html) (or [`ftp.ConnectWithKeyFile`](/process-functions/ftp-connectwithkeyfile.html) for key-based auth), then list and download the files it needs onto the MODLR filesystem. Store any credentials, keys or passphrases as [Secrets](/process-functions/security-getsecret.html) rather than hardcoding them in the process.

::: details Example
This example connects to a remote SFTP server using a key file, lists the files in a remote directory, and downloads any that haven't already been pulled down onto the MODLR filesystem (skipping any file that's already present, using [`datasource.fileSize`](/process-functions/datasource-filesize.html) as a simple "already downloaded" check).

```js
function pre() {
    // This function is called once before the processes is executed.
    // Use this to setup prompts.
    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.');

    let sftpClient = ftp.ConnectWithKeyFile(
        "sftp",
        "sftp.example.com",
        22,
        "modlr_user",
        "/path/to/key_file",
        security.getSecret("EXAMPLE_SFTP_PASSPHRASE")
    );

    let files = JSON.parse(
        sftpClient.Directory("/exports/latest")
    );

    files.forEach(item => {
        if (!item.isDirectory) {
            if (datasource.fileSize("/incoming/" + item.name) == 0) {
                console.log("/exports/latest/" + item.name);
                sftpClient.Download("/exports/latest/" + item.name, "/incoming/" + item.name);
            }
        }
    });
}

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.');
}
```

:::

See the [FTP Functions](/process-functions/ftp-functions.html) reference for the full set of `FTPClient` methods, including [`Directory`](/process-functions/ftpclient-directory.html), [`Download`](/process-functions/ftpclient-download.html) and [`IsConnected`](/process-functions/ftpclient-isconnected.html).

## Option 2: Have the platform upload via FTP

The external platform (or a scheduled export from it) uploads the file directly to the MODLR instance over [SFTP](/technical/ftp-sftp.html) or [FTPS (legacy)](/technical/ftp-ftps.html) - here MODLR is the server rather than the client. This is a good fit when the source system can push files on a schedule but doesn't expose anywhere for MODLR to pull from.

See [Accessing Files via FTP](/technical/ftp.html) for how to enable and connect to the FTP server, and the credentials to provide to the external platform.

## Option 3: Pull the file from a URL

If the source system instead exposes the file at a URL (for example, a signed export link or a simple HTTP endpoint), a Process can pull it directly onto the MODLR filesystem using [`web.download`](/process-functions/web-download.html), without needing an FTP transfer:

```js
web.download(
  "https://example.com/exports/latest.csv",
  "incoming/latest.csv"
);
```

`web.download` also accepts custom headers and an HTTP method/body, so it can be used against endpoints that require a token or a POST request to trigger the export - see the [`web.download` reference](/process-functions/web-download.html) for the full set of overloads.

## Option 4: Read and write via Amazon S3

If the source system exchanges files through an S3 bucket rather than FTP or a URL, a Process can talk to S3 directly using the built-in [`datasource.S3Bucket`](/process-functions/datasource-s3bucket.html) object - no separate library or manual request signing required. Store the access key and secret key as [Secrets](/process-functions/security-getsecret.html) rather than hardcoding them.

```js
let bucket = datasource.S3Bucket(
    security.getSecret("S3_ACCESS_KEY"),
    security.getSecret("S3_SECRET_KEY"),
    "example-bucket",
    "us-west-1"
);

let objects = bucket.list();

objects.forEach(object => {
    console.log(`Name: ${object.name}, Size: ${object.size}, Last Modified: ${object.last_modified}`);
    bucket.downloadFile(object.name, "incoming/" + object.name);
});
```

### Forwarding an S3 file to another API

Sometimes you don't want to land the file on the MODLR filesystem at all - for example, attaching a file straight from S3 onto a record in another system (an invoicing platform, say). `generatePresignedURL` gives a time-limited, unauthenticated link to the object, which can be fetched directly with [`web.get`](/process-functions/web-get.html) using its `base64Body` option, then turned into raw bytes with [`datasource.base64Bytes`](/process-functions/datasource-base64Bytes.html) ready to re-upload elsewhere:

```js
let objectKey = "jobs/example-file.pdf";
let presignedUrl = bucket.generatePresignedURL(objectKey, 3); // expires in 3 minutes

let response = web.get(presignedUrl, {}, true); // true = return the body as base64
let fileBytes = datasource.base64Bytes(response.body);

// fileBytes can now be sent as the body of a request to another API,
// e.g. web.post(otherApiUrl, headers, fileBytes)
```

See the [`S3Bucket`](/process-functions/s3bucket.html) reference for the full set of methods, including `uploadFile`.

## Reading the file in a Process

Once the file is on the MODLR filesystem, use the file-system [Datasource](/technical/datasources.html) functions to read and process it:

* [`datasource.files`](/process-functions/datasource-files.html) - list files in a directory, useful for picking up the latest export.
* [`datasource.readFile`](/process-functions/datasource-readFile.html) - read the file as a string, line array, JSON or XML.
* [`datasource.filelastmodified`](/process-functions/datasource-filelastmodified.html) - check when a file last changed, to avoid reprocessing the same export twice.
* [`datasource.fileSize`](/process-functions/datasource-filesize.html) - check whether a file has already been downloaded, as in the example above.
* [`datasource.renameFile`](/process-functions/datasource-renameFile.html) - move or archive a file once it's been processed.

```js
let files = datasource.files("incoming/");
for (let file of files) {
    let lines = datasource.readFile("incoming/" + file, "lines");
    for (let line of lines) {
        // parse each line and build dimensions / push data into cubes
    }
}
```

If the export is an Excel workbook (`.xlsx`/`.xlsm`) rather than a plain text file, `datasource.readFile` won't parse it - see [Reading Excel Files in MODLR](/technical/reading-excel-files.html) for reading these with the `workbook` object instead.