How to Validate and Import a Vendor Payment CSV from FTP into NetSuite

Build a scheduled Spojit workflow that downloads a vendor payment CSV from an FTP server, validates every row, and upserts the clean records into NetSuite while quarantining anything that fails the checks.

What This Integration Does

Finance teams often receive a daily or weekly vendor payment file dropped onto an FTP server by a bank, a payment processor, or an upstream accounting system. Keying those payments into NetSuite by hand is slow and error prone, and a single malformed row (a blank vendor reference, a non-numeric amount, a bad date) can corrupt a batch. This workflow pulls the file automatically, checks each row against rules you define, imports only the rows that pass, and emails you a summary of anything that did not so a person can follow up.

The workflow runs on a Schedule trigger at the cadence you set, so no one has to start it. On each run it downloads the latest CSV from FTP, parses it into rows, validates field by field, splits the rows into a clean set and a rejected set, and upserts the clean rows into NetSuite keyed by an external ID so a re-run never creates duplicates. Because the import is an upsert, running the same file twice is safe: existing payment records are updated in place rather than duplicated. The run leaves NetSuite holding the imported payments and your inbox holding a report of any rejected rows.

Prerequisites

An FTP connection in Spojit pointing at the server where the vendor file lands, with read access to the directory. See the FTP/SFTP connector guide.
A NetSuite connection authorized to read and write the record type you import into. See the NetSuite connector guide.
The exact remote path and filename of the CSV (for example /in/vendor-payments.csv), or a stable naming pattern you can target.
The column layout of the file, and the NetSuite record type and field names you will map each column to (including a column that uniquely identifies each payment to use as the external ID).
The email address that should receive the rejected-row report, added to your org allowlist under Settings → General → Email recipients if it is external. See configuring the email allowlist.

Step 1: Schedule the run with a Schedule trigger

Start a new workflow and set the Trigger node type to Schedule. Add a 5-field Unix cron expression and an IANA timezone so the run lands when the file is reliably present. For a weekday 8 AM Sydney run, use:

0 8 * * 1-5
Australia/Sydney

A single Schedule trigger can hold multiple schedules if the file arrives on more than one cadence. The trigger output is { scheduledAt }, which you can reference downstream as {{ trigger.scheduledAt }} for logging or in the report subject. See setting up a schedule trigger.

Step 2: Download the CSV with the FTP connector

Add a Connector node in Direct mode, choose the FTP connector, and pick the download-file tool. Set path to the remote file path and leave encoding at the default utf8 since a CSV is text. Map the output to a variable such as ftp_result. The file body comes back as {{ ftp_result.data.content }}, a UTF-8 string of the raw CSV.

If the filename changes each day, add a Connector node first calling the FTP list-directory tool against the directory, then a Transform node to pick the newest matching entry from {{ ftp_result.data.entries }} before downloading. For a fixed filename you can skip straight to download-file.

Step 3: Parse the CSV into rows

Add a Connector node in Direct mode, choose the csv utility connector, and pick the parse tool. Feed the downloaded text into it:

input:   {{ ftp_result.data.content }}
header:  true

Map the output to rows. With headers on, each row becomes an object keyed by your column names (for example {{ row.vendor_id }}, {{ row.amount }}, {{ row.invoice_ref }}, {{ row.payment_date }}), which makes the validation and mapping steps that follow far easier to read.

Step 4: Validate each row and split clean from rejected

Add a Loop node in ForEach mode over the parsed rows so you inspect one payment at a time. Inside the loop, validate the fields that matter most for an import. Use the validation utility connector for typed checks, for example its numeric tool against {{ row.amount }} and its iso-date tool against {{ row.payment_date }}, and a Condition node to confirm required fields such as {{ row.vendor_id }} and {{ row.invoice_ref }} are not empty.

Route the loop so passing rows continue to the import step and failing rows are collected for the report. A clean pattern is to build two arrays as you iterate: append valid rows to a cleanRows variable and failing rows (plus a short reason) to a rejectedRows variable using the array utility connector. This keeps the import set free of bad data and gives you an exact list to send back to the team. For a deeper validation pattern see validating and cleaning CSV data before import.

Step 5: Upsert clean payments into NetSuite

Add a Connector node in Direct mode, choose the NetSuite connector, and pick the upsert-record tool. Run this inside a Loop over {{ cleanRows }} so each validated payment is created or updated individually. upsert-record is keyed by external ID, which is what keeps re-runs idempotent: the same file imported twice updates the same records instead of duplicating them.

Set recordType to your target record type, set externalId to the unique identifier from the row (for example {{ row.invoice_ref }}), and map your columns into the body field:

recordType: vendorPayment
externalId: {{ row.invoice_ref }}
body: {
  "entity": "{{ row.vendor_id }}",
  "amount": {{ row.amount }},
  "trandate": "{{ row.payment_date }}",
  "memo": "Imported by Spojit {{ trigger.scheduledAt }}"
}

Use the exact field names from your NetSuite account; the example names above are illustrative. See using connector nodes in Direct mode for mapping details.

Step 6: Email a report of rejected rows

After the import loop, add a Condition node that checks whether {{ rejectedRows }} has any entries, then add a Send Email node on the true branch. Set Recipients to the finance reviewer, write a templated Subject such as Vendor payment import: {{ rejectedRows.length }} rows need review, and put a readable list of the rejected rows and their reasons in the Body. Set If sending fails to Continue anyway so a mail hiccup never fails an otherwise successful import. Send Email uses Spojit's built-in mail service, so no connection is needed. See using Send Email nodes.

Tips

Coerce amounts before import: NetSuite expects a numeric value, so trim currency symbols and thousands separators in a Transform node (or the text utility connector) rather than passing the raw string from the CSV.
Keep the external ID stable and unique. Choosing a column like an invoice or payment reference that never changes is what makes the upsert safe to re-run on the same file.
Set the schedule timezone to match where the file is produced, not where you sit, so the run never fires before the file lands.
Ask Miraxa, the intelligent layer across your automation, to scaffold the skeleton for you: "Build a workflow on a Schedule trigger that downloads /in/vendor-payments.csv with the FTP connector, parses it with the csv connector, loops over the rows, and upserts each into NetSuite." Then fine-tune the field mapping in the properties panel.

Common Pitfalls

Importing before validating. If you map straight from the parsed rows into upsert-record without the Step 4 checks, one bad amount or date can fail mid-batch and leave a partial import. Always split clean from rejected first.
Wrong NetSuite field names. Spojit passes body through as you map it; if a field name does not match your account's schema the record will not save. Confirm names against your NetSuite record type before going live.
Encoding mismatch on the download. Leave download-file at utf8 for a text CSV. Choosing base64 returns encoded bytes that the csv parse tool cannot read directly.
File not yet present. If the schedule fires before the upstream system drops the file, download-file fails. Schedule with margin, or add a get-file-info check and a Condition node to skip the run gracefully when the file is missing.

Testing

Before turning the schedule on, validate on a small scope. Place a short test CSV (a handful of rows including at least one deliberately bad row) on the FTP server, then run the workflow with the Run button and watch each step in the execution log. Confirm the FTP download returns the expected text, the csv parse output has the right columns, the bad row lands in rejectedRows, and only the clean rows reach upsert-record. Run it a second time against the same file and verify NetSuite updates the existing records rather than creating duplicates, which proves the external-ID upsert is idempotent. Once the import and the rejected-row email both look right, enable the schedule. See understanding execution logs.