How to Extract Structured Data from Inbound EDI and CSV Order Files with AI

Build a Spojit workflow that pulls partner order files from an FTP server on a schedule, uses an Agent-mode Connector node to normalize their varied EDI and CSV layouts into one consistent JSON schema, and creates matching NetSuite sales orders.

What This Integration Does

Trading partners rarely agree on a file format. One supplier drops a pipe-delimited EDI 850 export, another sends a comma-separated CSV with a different column order, and a third changes its headers every few months. Keying those orders into NetSuite by hand is slow and error prone. This workflow lets Spojit do the reading: it collects every new order file from a shared FTP directory, hands the raw text to an Agent-mode Connector node to map each layout onto a single agreed schema, and then writes a NetSuite sales order for each normalized order. Your team reviews exceptions instead of typing line items.

The workflow runs on a Schedule trigger, so it wakes up on a cron you set (for example every 30 minutes during business hours), lists the partner upload folder over the FTP connector, and downloads each file it finds. An Agent-mode Connector node turns the messy bytes into a strict JSON object that always has the same fields, and a Direct-mode Connector node creates the matching NetSuite record. Each run is independent; it processes whatever files are present at that moment, creates the orders, and (optionally) moves the processed files aside so the next run does not pick them up again. If a run fails partway, only unmoved files are retried next time.

Prerequisites

An FTP connection in Spojit with read access to the directory your partners upload to (set up under Connections - Add connection). The same connection needs write or rename permission if you want to move processed files.
A NetSuite connection authorized to create sales order records, with the SuiteTalk REST permissions your account uses for order entry.
The exact remote folder path your partners upload to, for example /inbound/orders/.
A short mapping cheat sheet you can paste into the agent prompt: how a customer reference in the file maps to a NetSuite customer internalId, and how partner SKUs map to NetSuite item internalId values. Have a few real sample files on hand for testing.
The target sales order recordType in NetSuite (typically salesOrder).

Step 1: Start the workflow on a Schedule trigger

Add a Trigger node and set its type to Schedule. Enter a 5-field Unix cron expression and an IANA timezone so the polling window matches your fulfilment hours. For example, every 30 minutes on weekdays:

Cron:     */30 * * * 1-5
Timezone: Australia/Sydney

A single Schedule trigger can hold multiple schedules if you want a different cadence overnight. The trigger output is {{ scheduledAt }}, which you can include in logs but is not otherwise needed downstream.

Step 2: List the partner upload folder over FTP

Add a Connector node in Direct mode, choose the FTP connector, and select the list-directory tool. Set path to your inbound folder:

path: /inbound/orders/

The tool returns a data.entries array where each entry has a name, type, size, and modifiedAt. Name the node output something clear like folder so you can reference {{ folder.data.entries }} in the next step. If the folder is empty, the array is empty and the loop below simply does nothing.

Step 3: Loop over each file

Add a Loop node in ForEach mode and point it at {{ folder.data.entries }}. Everything from here through the NetSuite create runs once per file, with the current entry available inside the loop body (for example {{ file.name }} if you name the iteration variable file). Keeping the per-file logic inside the loop means one malformed file does not block the others, and you can add a Condition at the top of the body to skip entries whose type is not file or whose name does not end in .csv, .edi, or .txt.

Step 4: Download the file contents

Inside the loop body, add a Connector node in Direct mode using the FTP connector and the download-file tool. Build the full remote path from the folder and the current entry name, and request UTF-8 text because EDI and CSV order files are plain text:

path:     /inbound/orders/{{ file.name }}
encoding: utf8

Name the output raw. The downloaded text is then available at {{ raw.data.content }}. If a partner ever sends a compressed or binary export, set encoding to base64 instead, but for the order-file use case keep it as utf8 so the agent receives readable text.

Step 5: Normalize the layout with an Agent-mode Connector node and a Response Schema

Add a Connector node and switch it to Agent mode. Agent mode lets the agent read the unpredictable file and decide how to map it, and the Response Schema forces the output into the same JSON shape every time so the next step is fully deterministic. Paste the file contents and your mapping rules into the prompt:

You are normalizing a trading-partner order file into a fixed schema.
The raw file content is below. It may be EDI (pipe or segment delimited)
or CSV with varying column order and header names.

Rules:
- customerRef is the partner's customer or account code from the file.
- Map each line: partnerSku -> the SKU/item code, qty -> ordered quantity,
  unitPrice -> price per unit if present (else null).
- poNumber is the partner purchase order number.
- Do not invent line items. If a field is missing, use null.

Raw file:
{{ raw.data.content }}

Define the Response Schema so the agent must return this exact structure:

{
  "type": "object",
  "properties": {
    "poNumber":   { "type": "string" },
    "customerRef":{ "type": "string" },
    "lines": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "partnerSku": { "type": "string" },
          "qty":        { "type": "number" },
          "unitPrice":  { "type": ["number", "null"] }
        },
        "required": ["partnerSku", "qty"]
      }
    }
  },
  "required": ["poNumber", "customerRef", "lines"]
}

Name the output order. You now have {{ order.poNumber }}, {{ order.customerRef }}, and {{ order.lines }} regardless of the original file format. Agent mode costs AI credits, so it is doing the one job that genuinely needs judgment: turning many shapes into one.

Step 6: Map references and build the NetSuite body with a Transform node

Add a Transform node to convert the normalized order into the exact body NetSuite expects, translating partner codes to NetSuite internal IDs and shaping the line items. Resolve {{ order.customerRef }} to a NetSuite customer internalId and each partnerSku to an item internalId using your mapping (a small lookup object works well, or use the NetSuite list-customers or run-suiteql tool in an earlier step if you need to look codes up live). Produce a body like this and name the output soBody:

{
  "entity":     { "id": "{{ customerInternalId }}" },
  "otherRefNum": "{{ order.poNumber }}",
  "item": {
    "items": [
      {
        "item":     { "id": "{{ line.itemInternalId }}" },
        "quantity": "{{ line.qty }}",
        "rate":     "{{ line.unitPrice }}"
      }
    ]
  }
}

Keeping the mapping in a Transform node means the agent never has to know your NetSuite internal IDs, and you can adjust the mapping without touching the prompt.

Step 7: Create the NetSuite sales order

Add a Connector node in Direct mode, choose the NetSuite connector, and select the create-record tool. Set recordType to your order record type and pass the mapped body:

recordType: salesOrder
body:       {{ soBody }}

NetSuite returns the created record so you can capture its internal ID. As an optional final step inside the loop, add another FTP Direct-mode node using the rename tool to move the processed file into an archive folder (from: /inbound/orders/{{ file.name }}, to: /inbound/processed/{{ file.name }}) so the next scheduled run does not reprocess it. You can also drop in a Send Email node to notify your fulfilment inbox with the new sales order number and the poNumber.

Tips

Keep the agent prompt focused on mapping only. Do the partner-code to internal-ID translation in the Transform node, where it is deterministic and easy to audit.
Use a Response Schema with required fields so a partly readable file produces an obvious error rather than a half-built order.
If volume is high, split the FTP listing into smaller batches by filtering on name prefixes per partner, and run separate schedules so one slow partner does not delay the rest.
Move or delete processed files with the FTP rename or delete-file tool so reruns stay idempotent. A run that does not move files will recreate orders on the next schedule.

Common Pitfalls

Reprocessing the same file. If you skip the archive step, every scheduled run downloads and re-creates the same orders. Always move processed files, or track handled file names in a database or Knowledge collection.
Timezone drift on the schedule. The cron uses the IANA timezone you set, not the server's. Confirm the zone so a "9am" run does not fire overnight for your warehouse.
Schema drift in partner files. A new column or renamed header can confuse a rigid parser, which is exactly why this workflow uses Agent mode. Still, review the agent output for the first few files from any new partner before trusting it.
Unmapped customer or SKU codes. If a partner code has no NetSuite internal ID, the create-record call will fail. Add a Condition after the Transform to route unmapped orders to a Send Email alert instead of attempting the create.

Testing

Before enabling the schedule, copy two or three real sample files into a test FTP folder and point the list-directory and download-file nodes at it. Run the workflow with the Run button and inspect each step in the execution log: confirm the file text reaches the agent, that {{ order.lines }} matches what is in the file, and that {{ soBody }} contains valid NetSuite internal IDs. Create a single order against a NetSuite sandbox first, verify the sales order and line items there, then switch the recordType target and connection to production and turn on the Schedule trigger.