Data Imports

Data Imports

Data Imports let you load external files into Essembi and create or update records using a configurable transform script. Use them for ERP extracts, spreadsheet loads, scheduled file drops, and API-driven integrations.

Data Imports are managed in Settings > Data Imports.

What a Data Import Does

Each Data Import configuration defines:

• Name — The label used in Settings and when calling the import from the API.
• File Type — The expected input format: Excel (.xlsx), JSON (.json), or Delimited (for example CSV / custom delimiters).
• Transform & Load Script — JavaScript that reads the uploaded/dropped file columns and creates or updates Essembi records.
• Active / Archived — Only active imports can be run.

When an import runs, Essembi:

1. Reads the file (or JSON body) into columns and rows
2. Runs the Transform & Load Script
3. Validates the resulting records
4. Saves inserts and updates into the target tables

Ways to Run a Data Import

• In the app — Open the Data Import in Settings and select Run Data Import, then upload a file.
• Via API — Call POST /Integrations/DataImport/{dataImportId} with a JSON body or an Excel/JSON/delimited file. See the API Library.
• Via Essembi FTP — Drop files onto the Essembi-provided FTP site. Essembi pulls those files into the matching Data Import configuration for insert/update processing.

Insert and Update Records via FTP File Upload

Essembi can provide an FTP site for your organization. Use FTP when an external system (ERP, warehouse system, partner, or scheduled job) should deliver files without calling the REST API directly.

Typical flow:

1. Essembi provides FTP connection details (host, credentials, and folder guidance) for your organization.
2. Your system uploads files (Excel, JSON, or delimited, depending on the Data Import File Type) to the Essembi FTP site.
3. Essembi pulls the file into the configured Data Import.
4. The Data Import Transform & Load Script inserts new records and/or updates existing records.

Setup checklist:

1. Create or open the Data Import in Settings > Data Imports.
2. Set the correct File Type (Excel, JSON, or Delimited).
3. Write the Transform & Load Script so it can insert and update from the file columns.
4. Test with Run Data Import using a sample file.
5. Coordinate with Essembi to map the FTP drop location to this Data Import.
6. Have the sending system upload files using the Essembi-provided FTP credentials.

FTP is a delivery method. The Data Import configuration still controls how columns map into inserts and updates.

Creating a Data Import

1. Go to Settings > Data Imports.
2. Create a new Data Import and give it a clear name (for example, “Sage X3 Work Orders”).
3. Choose the File Type:
   • Excel — First worksheet of an .xlsx file
   • JSON — Structured JSON with columns and row data
   • Delimited — Text files such as CSV; configure column and row delimiters (including custom escape sequences when needed)
4. Write the Transform & Load Script.
5. Save the configuration.
6. Use Manage Access to grant teams Create / Edit / Run rights as needed.

Transform & Load Script Basics

The script has access to:

• dataImport — Columns and rows from the incoming file
• database — Query existing records and prepare inserts/updates
• web — Supporting web helpers when needed

Common dataImport helpers:

• hasColumn('Column Name')
• getColumnValue('Column Name', rowIndex)
• getColumnData('Column Name')
• rowCount
• createRecord('Table Name')
• trackRecord(record) — Marks a record to be inserted or updated by the load step

Common pattern for insert/update:

1. Validate required columns exist.
2. Collect key values from the file (for example part numbers or work order numbers).
3. Query existing records with database.queryTable(...).addFilter(...).execute().
4. For each row: update the existing record, or create a new one.
5. Call dataImport.trackRecord(record) for every record that should be saved.

Example pattern:

if (!dataImport.hasColumn('Part'))
{
    throw "Data must have `Part` Column.";
}

const parts = dataImport.getColumnData('Part');
const existing = database.
    queryTable('Formula').
    addFilter('Part', 'ContainedIn', parts).
    execute();

const byPart = {};
existing.forEach(r => {
    byPart[r.getValue('Part').toLowerCase()] = r;
});

for (let i = 0; i < dataImport.rowCount; ++i)
{
    const part = dataImport.getColumnValue('Part', i);
    let record = byPart[part.toLowerCase()];

    if (!record)
    {
        record = dataImport.createRecord('Formula');
        record.setValue('Part', part);
    }

    record.setValue('Standard Cost', dataImport.getColumnValue('Standard Cost', i));
    dataImport.trackRecord(record);
}

Running a Data Import in the App

1. Open the Data Import.
2. Select Run Data Import.
3. Upload a file that matches the configured File Type.
4. Review any validation errors.
5. Confirm the load when the transform succeeds.

Validation and table validation scripts still apply to imported records, just as they do for forms and API creates/edits.

Security for Data Imports

Data Imports use role-based security:

• Create / Edit All / Edit Specific — Maintain Data Import configurations
• Run All / Run Specific — Execute imports

Teams also need Access Settings to reach the Settings menu. Use the security icon / Manage Access on the Data Import to grant team rights, then validate with View Access on the team.

Choosing a Delivery Method

Method	Best when
Run in app	One-off loads, testing a new mapping, or manual spreadsheet imports
API (/Integrations/DataImport/{id})	Another system can call Essembi directly with JSON or a file
Essembi FTP	The source system prefers file drop over HTTP, or already produces scheduled flat files

For endpoint details, request bodies, and examples, see the API Library.