How to Reconcile a Time and Expense CSV into NetSuite Billable Charges

Build a Spojit workflow that runs every night, downloads the weekly time and expense CSV from your FTP server, validates each row, creates a billable charge record in NetSuite for every line, and posts an import summary to Slack.

What This Integration Does

Professional services teams usually collect billable hours and reimbursable expenses in a spreadsheet that gets exported to a CSV and dropped on an FTP server at the end of each week. Keying those lines into NetSuite by hand is slow and error prone, and a single mistyped customer or amount throws off the next invoice run. This workflow removes the manual step: it picks up the file automatically, checks that every row is clean, pushes one billable charge record into NetSuite per line, and tells your team exactly how many rows landed and how many were skipped.

The workflow runs on a Schedule trigger overnight. On each run it downloads the latest CSV from FTP, parses and validates it with the csv connector, and then loops over the rows. For each valid row it calls the netsuite connector's create-record tool in Direct mode to create a billable charge, collects the results, and finishes by sending a one-message summary to Slack. The workflow leaves your NetSuite account with new charge records and leaves the source file untouched, so re-running the same file would create duplicate charges unless you scope the schedule to one file per week (see Common Pitfalls).

Prerequisites

An ftp connection pointing at the server where your weekly export lands, with read access to the export directory.
A netsuite connection with permission to create the record type you bill against (for example a charge or journal record), and the internal IDs you will reference: the customer/entity, the item or charge type, and any required custom fields.
A slack connection and the channel ID (or name) where the import summary should post.
A known, stable CSV layout with columns such as employee, customer, date, hours, rate, amount, and memo. Decide which columns are required before you build.
A NetSuite sandbox or a small test file so you can validate before turning the schedule on.

Step 1: Add the Schedule trigger

Create a new workflow and add a Trigger node, then set Trigger Type to Schedule. Spojit schedules use a 5-field Unix cron expression plus an IANA timezone. To run at 02:00 every night, set the cron to 0 2 * * * and the timezone to your local zone, for example Australia/Sydney. The trigger output is {{ trigger.scheduledAt }}, which you can drop into the Slack summary later. If your export only lands on weekdays, narrow the cron to 0 2 * * 1-5 instead.

Step 2: Download the CSV from FTP

Add a Connector node on the ftp connector in Direct mode and choose the download-file tool. Set path to the remote file, for example /exports/time-expense/weekly.csv, and set encoding to utf8 since the CSV is text. The tool returns the file under {{ ftp_result.data.content }} as a string, along with {{ ftp_result.data.size }}. If the filename changes each week (for example it includes a date), use the date connector's format tool first to build the path, then template it into path.

Step 3: Parse the CSV into rows

Add a Connector node on the csv connector in Direct mode and choose the parse tool. Map csv to {{ ftp_result.data.content }}, leave header as true so each row becomes an object keyed by your column names, and keep delimiter as , unless your export uses tabs or semicolons. The tool returns {{ csv_parsed.items }} (the array of row objects) and {{ csv_parsed.count }} (the row count). Each row is now addressable as {{ row.customer }}, {{ row.amount }}, and so on inside the loop.

Step 4: Validate and drop bad rows

Before you write anything to NetSuite, strip out rows that would fail. Add a Connector node on the csv connector in Direct mode with the filter tool. Map csv to {{ ftp_result.data.content }}, set column to amount, set operator to gt, and set value to 0 so only rows with a positive billable amount continue. You can chain a second filter node (for example customer with operator not_equals and value blank) to drop rows missing a customer. After filtering, re-run parse on the filtered output, or feed the filtered CSV straight into a final parse node, so the loop iterates only clean rows. Keep the original {{ csv_parsed.count }} so you can report how many rows were skipped.

If you need richer checks than the filter operators offer, you can validate individual fields with the validation connector (for example numeric or iso-date) inside the loop and use a Condition node to skip a row.

Step 5: Loop and create a billable charge in NetSuite

Add a Loop node set to ForEach and point it at the validated rows, for example {{ csv_clean.items }}. Inside the loop body, add a Connector node on the netsuite connector in Direct mode and choose the create-record tool. Set the record type to the charge record you bill against and map the body fields from the current row. A typical mapping looks like this:

{
  "type": "charge",
  "fields": {
    "entity": "{{ row.customer }}",
    "item": "{{ row.item }}",
    "quantity": "{{ row.hours }}",
    "rate": "{{ row.rate }}",
    "amount": "{{ row.amount }}",
    "memo": "{{ row.employee }} - {{ row.date }} - {{ row.memo }}"
  }
}

Use the internal IDs your NetSuite account expects for entity and item. If your CSV carries names rather than IDs, add a netsuite Direct-mode node with list-customers or list-items earlier in the run and build a lookup map with a Transform node so the loop resolves each name to an ID. The create-record tool returns the new record's identifier, which you can collect into a running list to count successes.

Step 6: Tally results with a Transform node

After the loop, add a Transform node to assemble the numbers you want in the summary: total rows in the file, rows that passed validation, charges created, and any rows the loop could not write. Reference the upstream counts such as {{ csv_parsed.count }} (rows in the file) and {{ csv_clean.count }} (rows that passed), and the length of the loop's collected results. Produce a small object like {{ summary.created }}, {{ summary.skipped }}, and {{ summary.total }} for the next step. Keeping this in one Transform node makes the Slack message easy to template and easy to read in the run logs.

Step 7: Post the import summary to Slack

Add a Connector node on the slack connector in Direct mode and choose the send-message tool. Set channel to your finance or ops channel and template the text from the Transform output:

Time and expense import complete ({{ trigger.scheduledAt }}).
File rows: {{ summary.total }}
Charges created in NetSuite: {{ summary.created }}
Rows skipped (failed validation): {{ summary.skipped }}

If you would rather email the summary, you can use a Send Email node instead, which sends from Spojit's built-in mail service with no connection required. Slack is the better fit here because the team usually watches a channel for nightly batch results.

Tips

Run NetSuite calls in Direct mode, not Agent mode. The mapping is deterministic and Direct mode does not spend AI credits, which matters when a single file can produce hundreds of create-record calls.
Use the memo field on each charge to stamp the source row (employee, date, original line) so you can trace any charge back to the CSV during a reconciliation.
If your provider is slow under load, keep the loop sequential rather than fanning out with a Parallel node, so you stay within NetSuite's concurrency limits.
Hold multiple schedules on one trigger if different regions export at different local times; each schedule fires the same workflow.

Common Pitfalls

Duplicate charges on re-run. This workflow does not move or rename the source file, so re-running the same CSV creates the same charges again. After a successful run, archive or rename the file with the ftp connector's rename tool, or include a unique key in each charge's memo and check for it before creating.
Names vs internal IDs. NetSuite create-record expects internal IDs for entity and item. Passing a display name will fail or create an unlinked record. Resolve names to IDs before the loop.
Timezone drift on the schedule. Cron without a timezone, or the wrong IANA zone, can run the workflow before the file lands. Match the timezone to where the export is produced.
Silent type coercion. The parse tool infers types, so a stray currency symbol or thousands separator can turn an amount into text and slip past a numeric filter. Strip formatting in a Transform node, or validate with the validation connector's numeric tool, before mapping amount.

Testing

Before enabling the schedule, point the download-file path at a small test file with three or four rows, including one deliberately bad row (zero amount or missing customer). Run the workflow with the Run button and step through the execution logs: confirm the parse count matches the file, the filter step drops the bad row, the loop creates exactly the expected number of charges in your NetSuite sandbox, and the Slack message reports the right created and skipped counts. Once the numbers reconcile against the sandbox, switch the NetSuite connection to production, repoint the path at the real export, and turn the schedule on. You can also ask Miraxa, the intelligent layer across your automation, "Why did my last run create fewer charges than rows in the file?" to investigate any gap.