How to Export a Daily NetSuite Open Invoices Report to FTP as CSV

Build a Spojit workflow that runs every morning, queries open invoices from NetSuite with SuiteQL, converts the rows to a CSV file named with today's date, and drops it into your finance team's FTP folder for downstream BI ingestion.

What This Integration Does

Finance and analytics teams often need a fresh extract of unpaid invoices to feed a data warehouse, a BI dashboard, or a collections spreadsheet. Pulling that report by hand from NetSuite every morning is slow and easy to forget. This workflow does it on a schedule: it asks NetSuite for every invoice with an outstanding balance, turns the result into a clean CSV file, stamps the filename with the run date, and uploads it to a folder your BI tooling already watches. By the time the team logs in, a dated open-invoices-YYYY-MM-DD.csv file is waiting on the FTP server.

The run model is a daily Schedule trigger. Each morning the trigger fires, a Connector node runs a SuiteQL query against NetSuite, a Transform node and the csv connector shape the rows into CSV text, the date connector produces the date stamp, and a final Connector node uploads the file over FTP. Each run is independent and stateless: it always reflects the live open-invoice list at the moment it runs. Because the FTP upload-file tool overwrites a file of the same name, a re-run on the same day simply replaces that day's file rather than creating a duplicate.

Prerequisites

A NetSuite connection in Spojit with token-based auth, and a role that can run SuiteQL queries against the transaction and transactionline tables (read access to invoices).
An FTP connection to your finance server, with write permission to the destination folder (for example /reports/finance/).
The csv and date connectors, which are built in and need no authentication.
A timezone for the schedule (an IANA name such as Australia/Sydney or America/New_York) so the report lands at the right local hour.
The exact remote folder path your BI pipeline reads from.

Step 1: Add the Schedule trigger

Create a new workflow and set the Trigger node type to Schedule. Add a 5-field Unix cron expression and an IANA timezone. To run at 06:00 every weekday morning, use:

0 6 * * 1-5

Set the timezone to your finance team's location, for example Australia/Sydney. A Schedule trigger can hold more than one schedule if you need extra run times. The trigger output is {{ scheduledAt }}, the timestamp of the run, which you can use later if you prefer the file date to match the scheduled time rather than the current clock.

Step 2: Query open invoices with NetSuite run-suiteql

Add a Connector node in Direct mode on the netsuite connector and choose the run-suiteql tool. Direct mode is deterministic and costs no AI credits, which is what you want for a predictable scheduled extract. In the query field, write a SuiteQL statement that returns the columns your BI layer expects. An open invoice is one whose status is open and whose remaining amount is greater than zero:

SELECT
  t.tranid        AS invoice_number,
  t.trandate      AS invoice_date,
  t.duedate       AS due_date,
  e.entityid      AS customer,
  t.foreigntotal  AS total,
  t.foreignamountunpaid AS amount_due,
  t.currency      AS currency
FROM transaction t
JOIN entity e ON e.id = t.entity
WHERE t.type = 'CustInvd'
  AND t.status = 'CustInvc:A'
  AND t.foreignamountunpaid > 0
ORDER BY t.duedate ASC

Set limit to its maximum of 1000 rows per page. The tool returns an envelope of the form { success, status, data }; the result rows live under data.items. Bind the node output to a variable such as {{ invoices }} so later steps can read {{ invoices.data.items }}. Confirm field and table names for your account in the NetSuite Analytics Data Source Schema Browser, since customized accounts can differ.

Step 3: Shape the rows with a Transform node

SuiteQL returns each row as an object whose keys match your column aliases, but the values can include nulls or numbers you want formatted before they reach the CSV. Add a Transform node that reads {{ invoices.data.items }} and produces a clean array of flat objects with exactly the columns you want, in order. Map each row to fields like invoice_number, customer, invoice_date, due_date, amount_due, and currency, replacing any null with an empty string and rounding monetary values to two decimals. Bind the result to {{ rows }}. Keeping this shaping in its own step means the CSV stays a pure formatting step and your column contract is easy to read and change.

Step 4: Build the date stamp with the date connector

Add a Connector node in Direct mode on the date connector and choose the format tool. In the date field, pass the trigger time {{ scheduledAt }} (or use the now tool for the current clock). Set format to:

YYYY-MM-DD

Set timezone to the same IANA zone as your schedule, for example Australia/Sydney, so the stamp reflects local date rather than UTC, which matters around midnight. The tool returns { result }; bind it to {{ stamp }} so you can reference {{ stamp.result }} when naming the file.

Step 5: Convert the rows to CSV

Add a Connector node in Direct mode on the csv connector and choose the from-json tool. Set data to {{ rows }}, the cleaned array from the Transform node. To lock the column order and headers, set the columns field to an explicit list:

["invoice_number", "customer", "invoice_date", "due_date", "amount_due", "currency"]

Leave header as true so the first line carries column names, keep delimiter as ,, and set quotes to true if any customer name might contain a comma. The tool returns { csv, rows, encoding }; bind the output to {{ report }}. The CSV text is then available as {{ report.csv }}.

Step 6: Upload the file to FTP

Add a final Connector node in Direct mode on the ftp connector and choose the upload-file tool. Set content to {{ report.csv }} and leave encoding as utf8, since the CSV is plain text. Build the remote path from your destination folder and the date stamp:

/reports/finance/open-invoices-{{ stamp.result }}.csv

The upload-file tool overwrites any existing file at that path, so a same-day re-run cleanly replaces the file rather than piling up copies. The tool returns { success, data } where data.size is the number of bytes written, which you can log to confirm a non-empty file landed. Save the workflow, then enable it so the schedule starts firing.

Tips

If your invoice list can exceed 1000 rows, page through the data: call run-suiteql in a Loop node, incrementing offset by your limit each pass, and concatenate the pages before the CSV step.
To send the same file to more than one destination at once, fan the upload out with a Parallel node, one branch per FTP folder, all reading the same {{ report.csv }}.
Add a Send Email node after the upload to notify the finance team that the day's file is ready, including the row count from {{ report.rows }} in the body.
Use Miraxa, the intelligent layer across your automation, to scaffold the canvas quickly. Try a prompt like "Add a Connector node on the date connector that formats {{ scheduledAt }} as YYYY-MM-DD and connect it before the FTP upload."

Common Pitfalls

Timezone drift at midnight. If you format the date in UTC but run in a different local zone, a file generated just after midnight local time can carry yesterday's or tomorrow's date. Always set the same IANA timezone on both the Schedule trigger and the date format call.
Status code drift between accounts. The SuiteQL status literal for an open invoice (CustInvc:A) and the transaction type can differ in customized accounts. Verify the exact values against your own data in the Schema Browser before trusting the filter.
Unquoted commas. Customer names and addresses frequently contain commas. If you leave quotes off, those fields can break column alignment downstream. Turn quotes on when any text field may contain the delimiter.
FTP path or permissions. A missing destination folder or a read-only account causes upload-file to fail. Confirm the folder exists (use the create-directory tool once if needed) and that your FTP connection can write to it.

Testing

Before enabling the daily schedule, validate on a small scope. Temporarily add FETCH FIRST 5 ROWS ONLY to the SuiteQL query so you work with a handful of invoices, then run the workflow once with the Run button (you can keep the Schedule trigger and still trigger a manual run for testing). Open the execution log to inspect each step: confirm {{ invoices.data.items }} holds rows, that {{ rows }} is shaped correctly, that {{ stamp.result }} reads like 2026-06-21, and that the FTP step reports a non-zero data.size. Download the uploaded file from the FTP server and open it in a spreadsheet to verify headers and values. Once the small extract looks right, remove the FETCH FIRST clause and leave the workflow enabled.