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 reads the file (or JSON body) into columns and rows, runs the Transform & Load Script, validates the resulting records, and 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: Essembi provides FTP connection details (host, credentials, and folder guidance) for your organization; your system uploads files to the Essembi FTP site; Essembi pulls the file into the configured Data Import; the Data Import Transform & Load Script inserts new records and/or updates existing records.

Setup checklist: create or open the Data Import in Settings > Data Imports; set the correct File Type; write the Transform & Load Script so it can insert and update from the file columns; test with Run Data Import using a sample file; coordinate with Essembi to map the FTP drop location to this Data Import; have the sending system upload files using the Essembi-provided FTP credentials.

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), or Delimited (text files such as CSV, with configurable column and row delimiters).
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), and web (supporting web helpers when needed).

Common dataImport helpers include hasColumn, getColumnValue, getColumnData, rowCount, createRecord, and trackRecord (marks a record to be inserted or updated by the load step).

Common pattern for insert/update: validate required columns exist, collect key values from the file, query existing records with database.queryTable(...).addFilter(...).execute(), then for each row either update the existing record or create a new one, calling trackRecord for every record that should be saved.

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 to maintain Data Import configurations, and Run All / Run Specific to 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

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