Importing ETL and payroll journals into NetSuite with CSV import tasks

Calling task.create({ taskType: task.TaskType.CSV_IMPORT }) takes five lines. Getting a month of journals into NetSuite reliably takes a lot more. You need the right files, in the right shape, and when the import finishes, NetSuite doesn't tell you how many rows made it in.

In this article, I'll walk through two journal feeds I built for a client, and the CSV task pipeline they share:

• ETL Journal IN: a general ledger ETL system produces a batch of four related files every month. All four must be imported, and they must all be from the same period.
• Payroll Journal IN: the payroll provider produces one summary report per month. It's a report for people, not a journal, so it has to be turned into debit and credit lines first.

Client details are anonymized and the code is simplified.

We'll cover:

• Picking a complete, settled batch of files from SFTP
• Turning each file into a CSV Task record with an explicit state machine
• Transforming the ETL files and the payroll report for a saved CSV import
• Processing many files in one job without one bad file stopping the rest
• What task.checkStatus() doesn't return, and how to count imported journals anyway
• Three search bugs that made reconciliation silently return zero

Prerequisites

You should know SuiteScript 2.1, user event scripts, Map/Reduce scripts, N/task, N/file, and saved CSV imports (import maps). Some Node.js helps for the gateway part. My article on NetSuite concurrency limits and retries gives useful background for the integration side.

The architecture

The upstream systems drop files on an SFTP server. NetSuite can't read SFTP directly in this setup, so a small Node.js integration gateway sits in between. It polls NetSuite for integration jobs, claims one, pulls the files from SFTP, and sends them back to a RESTlet when it completes the job.

On the NetSuite side, every file becomes a CSV Task record. The task goes through transformation, a CSV import, and a final count.

Use case 1: picking a complete ETL batch

The ETL system writes four files per month: an aggregation file, a reclassification file, and two consolidation files. Their names end with the period, for example ..._Aggregation_202509.csv. Together they make one month's journals. Importing three of the four, or mixing September's aggregation with August's consolidation, gives the wrong ledger.

The gateway's job is to pick the right set. It does that in three steps, from strictest to loosest:

1. Period suffix: if all four file types exist for the job's target period, use them
2. Latest complete period: otherwise, use the newest period that has all four types
3. Last modified: otherwise, take the newest file of each type, whatever its period

const REQUIRED_TYPES = ['Aggregation', 'Reclassification', 'ConsolidateC', 'ConsolidateD']

function selectEtlBatch(files, { nowMs, minAgeMs, targetPeriod }) {
  // Ignore files that are still being written
  const settled = files.filter(
    (f) => isEtlFile(f.name) && nowMs - Number(f.modifyTime) >= minAgeMs
  )
  if (settled.length === 0) throw new Error('No settled ETL source file found')

  const forTarget = latestByType(settled, (f) => periodOf(f.name) === targetPeriod)
  if (REQUIRED_TYPES.every((t) => forTarget.has(t))) {
    return pick(forTarget, targetPeriod, 'period_suffix')
  }

  const complete = completePeriods(settled).sort((a, b) => b.period - a.period)[0]
  if (complete) return pick(complete.files, complete.period, 'latest_complete_period')

  const latest = latestByType(settled)
  if (!REQUIRED_TYPES.every((t) => latest.has(t))) {
    throw new Error(`No complete ETL file set found. Expected: ${REQUIRED_TYPES.join(', ')}`)
  }
  return pick(latest, null, 'last_modified')
}

Three details here came from how files really arrive:

• A grace period. A file modified in the last few minutes (five by default, set by an environment variable) may still be uploading. Ignoring it avoids importing half a file.
• The strategy travels with the files. The gateway sends selectionStrategy and the period to NetSuite, and both are kept in the job's result. When finance asks "why did August's job import September's files?", the answer is on the record: last_modified.
• .csv or .txt. The upstream system sometimes wrote the same content with a .txt extension. The file filter accepts both.

Each file also gets a SHA-256 fingerprint of its content, so NetSuite can tell whether a file it received is the same file it saw before.

Use case 2: picking the payroll report

Payroll is simpler on the SFTP side. There's one file per month, named with a month-year token, like PAYROLL_SEP26.csv. The gateway builds the token from the job's date and looks for that file. If it isn't there, it falls back to the most recently modified matching file, and says so:

const PAYROLL_FILE = /^PAYROLL_([A-Za-z]{3}\d{2})\.csv$/i

function selectPayrollFile(files, { targetToken }) {
  const candidates = files
    .filter((f) => PAYROLL_FILE.test(f.name))
    .sort((a, b) => Number(b.modifyTime) - Number(a.modifyTime))

  if (candidates.length === 0) throw new Error('No payroll journal file found')

  const exact = candidates.find((f) => f.name.match(PAYROLL_FILE)[1].toUpperCase() === targetToken)
  return exact
    ? { file: exact, selectionStrategy: 'current_job_period' }
    : { file: candidates[0], selectionStrategy: 'last_modified' }
}

The selection functions are pure, so they're covered by unit tests without an SFTP server: the exact-month file wins, the fallback picks the latest file, and a folder with no matching name is rejected.

A CSV Task per file

Back in NetSuite, the RESTlet that completes the job stores each file in the File Cabinet and creates one CSV Task record per file. An ETL job creates four tasks, a payroll job creates one.

A CSV Task stores the raw file, the template to run, the massaged file, the import task, counts, a message, and a progress percentage. It has two status fields:

• Status is short, for people: Pending, Submitted, Completed, Error
• Execution state is detailed, for scripts

The first version had only the Status field, and scripts found their work by matching status text. Splitting the two let the pipeline add steps without changing what users filter and report on.

The percentages are rough, but they matter. A finance user who sees "60%, Import Submitted" waits. A user who sees "Pending" for ten minutes opens a ticket.

Processing many files in one job

For the ETL and payroll feeds, the RESTlet processes each task right away, as part of completing the job. It transforms the file and submits the CSV import. The import itself runs asynchronously in NetSuite's queue, so the request doesn't wait for it.

The important part is that the files are independent. Each one is processed in its own try/catch, and the job result lists what happened to each:

function processCsvTasks(taskIds) {
  return taskIds.reduce(
    (result, taskId) => {
      try {
        const r = processTask(taskId)
        result.processed.push({ csvTaskId: taskId, importTaskId: r.importTaskId })
      } catch (e) {
        result.failed.push({ csvTaskId: taskId, error: e.message })
      }
      return result
    },
    { processed: [], failed: [] }
  )
}

const { processed, failed } = processCsvTasks(taskIds)
job.errorMessage = failed
  .map((f) => `Task ${f.csvTaskId}: ${f.error}`)
  .join('; ')
  .slice(0, 300) // the job's error field is a 300-character text field
if (processed.length === 0) job.status = STATUS.ERROR

If the reclassification file has a missing column, the other three ETL files still import, and the job's message says which task failed and why. The job is only marked Error when no file could be processed. That task can then be fixed and reprocessed on its own, without touching the three that worked.

Not every feed was ready for this at go-live. For two other journal feeds, automatic processing was turned off: the files are stored, and the job reports manual_only instead of producing a stream of Error tasks.

Tasks created by hand

Users can also create a CSV Task in the UI: upload a raw file, pick a template, save. For these, a user event on the CSV Task queues the work on a processor Map/Reduce.

The processor has a single deployment, on purpose. Two runs picking up the same Pending task would import the same journals twice. With one deployment, task.submit() throws when the processor is already running, and the user event treats that as "the running processor will pick this up":

function queueTask(taskId) {
  updateTask(taskId, { executionState: STATE.QUEUED, message: 'Queued for CSV processing' })
  try {
    updateTask(taskId, { processTaskId: submitProcessor() })
  } catch (e) {
    if (!/already running and cannot be started/i.test(e.message)) throw e
    updateTask(taskId, { message: 'Queued for CSV processing; waiting for active processor' })
  }
}

To close the gap where a task is queued just after the running processor collected its input, the processor's summarize searches for remaining Pending or Queued tasks and submits itself again if it finds any. The monitor Map/Reduce uses the same self-chain.

Transforming the ETL files

The ETL files are close to what the import needs. The template checks that every required source column exists, then maps each row to the import map's columns and fills fixed defaults, like currency and accounting book:

function transformEtl(content, options) {
  const rows = parseCsv(content)
  if (rows.length < 2) throw new Error('ETL raw file is empty')

  const index = indexByHeader(rows[0])
  const missing = Object.values(ETL_COLUMN_MAP).filter((col) => index[col] === undefined)
  if (missing.length) throw new Error(`ETL raw file is missing required columns: ${missing.join(', ')}`)

  const out = [ETL_HEADERS.slice()]
  rows.slice(1).forEach((row) => {
    if (isBlankRow(row)) return
    const get = (col) => String(row[index[col]] || '').trim()
    out.push([
      ...ETL_DEFAULTS,
      formatDate(get(ETL_COLUMN_MAP.accountingDate)),
      get(ETL_COLUMN_MAP.account),
      // ...segments, amounts, memo, line memo
    ])
  })

  appendCsvJobIdColumn(out, options.csvJobId) // used for reconciliation, see below
  return { rows: out, content: toCsv(out), successCount: out.length - 1, failCount: 0 }
}

A missing column throws, and the whole file fails. That's on purpose: a file with a missing column is a different file format, not a few bad rows.

The massaged file's column headers are a contract with the saved CSV import. Later, the client found that the header memo and line memo were swapped: the header memo was coming from the source column meant for lines, and the other way around. The fix was two lines in ETL_COLUMN_MAP. The output headers didn't change, so the import map in NetSuite didn't need touching. Only the data under the headers changed.

Transforming the payroll report

The payroll file is a report built for people. The first rows are titles, the column headers are on the fifth row, there's one row per cost centre, each pay item is a column, and the last row is a Grand Total. A journal needs the opposite: one line per account, cost centre, and pay item, with a debit or a credit.

So the payroll template pivots the report:

function transformPayroll(content, options) {
  const rows = parseCsv(content)
  const header = rows[4] // headers are on the 5th row
  const get = columnGetter(header)

  const dataRows = rows
    .slice(5)
    .filter((r) => get(r, 'Cost Centre') && get(r, 'Cost Centre') !== 'Grand Total')

  const out = []
  dataRows.forEach((row) => {
    const costCentre = get(row, 'Cost Centre')
    PAY_ITEMS.forEach((item) => {
      const amount = Math.abs(toNumber(get(row, item.column)))
      if (amount <= 0) return
      out.push(
        journalLine({
          account: item.account,
          costCentre,
          debit: item.isDebit ? amount : 0,
          credit: item.isDebit ? 0 : amount,
          lineMemo: `${costCentre} ${item.label} ${periodLabel(options.fileName)}`,
        })
      )
    })
  })

  return finalize(out, options)
}

PAY_ITEMS covers salary, net salary, employer and employee pension contributions, leave payments, and the other pay items, each with its account and side. A cost centre that has no segment mapping is skipped. The period label comes from the file name's month-year token.

Balancing the journal

A Journal Entry must balance, and a payroll report doesn't always add up exactly once it's been split into many lines and rounded. If debits and credits differ by a cent, the CSV import rejects the whole journal.

So after building the lines, the template totals both sides, rounded to two decimals. If they differ, it adds one balancing line to a designated suspense account, on whichever side is short:

const totalDebit = sum(out, 'debit')
const totalCredit = sum(out, 'credit')
const difference = Number((totalDebit - totalCredit).toFixed(2))

if (difference !== 0) {
  out.push(
    journalLine({
      account: SUSPENSE_ACCOUNT,
      debit: difference < 0 ? Math.abs(difference) : 0,
      credit: difference > 0 ? difference : 0,
      lineMemo: 'Journal Import Created',
    })
  )
}

The journal imports, and the difference is visible as its own line with a fixed memo, so finance can find and review it. That's better than a rejected import, and much better than spreading a rounding difference silently across other lines.

A header name for payroll journals

Finance also wanted every imported payroll journal to carry a readable header name with the payroll period and upload date. That's set by a beforeSubmit user event on Journal Entry, deployed with the CSV Import execution context so it runs during the import.

Two rules keep it safe: it only applies to payroll journals, and it refreshes the value on a CSV re-import but never overwrites a value someone set by hand in any other context.

Submitting the import

After the transform, processTask saves the massaged file and submits the import:

function submitCsvImport(mappingId, fileId, ctx) {
  return task
    .create({
      taskType: task.TaskType.CSV_IMPORT,
      mappingId,
      importFile: file.load({ id: fileId }),
      name: `${ctx.name} Import - ${runtime.getCurrentUser().email}`,
    })
    .submit()
}

A few small rules came from real use:

• Stable file names. The massaged file is always {rawName}_result.csv, and an existing file with that name is deleted before saving. Reprocessing replaces the output instead of piling up copies.
• The user in the job name. On NetSuite's CSV Import Job Status page, people can find their own imports without opening every row.
• Rejected rows block the import. If the template rejected any row, the task stops at Error with the massaged file saved, so nobody imports a partial month by accident.
• A missing permission keeps the work. If the role lacks the Import CSV File permission, the task stops at Massaged Ready with a message naming the permission. Someone with the right role can import the file by hand.

What checkStatus() doesn't tell you

A monitor Map/Reduce calls task.checkStatus() for every running import. I expected a progress value and a summary like "120 of 120 records imported". For CSV import tasks, NetSuite returns only this:

const status = task.checkStatus({ taskId: importTaskId })
status.status // 'PENDING' | 'PROCESSING' | 'COMPLETE' | 'FAILED'
// No row counts, no percent complete, no message.

COMPLETE means the job finished. It doesn't mean every row was imported. An import where rows were rejected, for example because the period was closed, can still report COMPLETE. The per-row results exist only as a response file on the CSV Import Job Status page.

Counting what was actually imported

To count the records, you have to find exactly the journals one import created. Searching by date, memo, and subsidiary is fragile: two ETL files for the same period look alike to that search.

The reliable answer is a marker in the data. Both templates add a CSV Job ID column to every massaged row, holding the CSV Task's internal ID. The import map writes it to a custom line field on the Journal Entry. After the import, one search finds every line from this task:

function searchJournalLinesByCsvJobId(csvJobId) {
  const ids = new Set()
  let lineCount = 0
  const runner = search
    .create({
      type: 'journalentry',
      filters: [['mainline', 'is', 'F'], 'AND', ['custcol_csv_job_id', 'is', csvJobId]],
      columns: ['internalid'],
    })
    .run()

  for (let start = 0; ; start += 1000) {
    const page = runner.getRange({ start, end: start + 1000 })
    page.forEach((r) => {
      lineCount++
      ids.add(r.getValue('internalid'))
    })
    if (page.length < 1000) break
  }
  return { lineCount, journalEntryIds: Array.from(ids) }
}

Imported is the number of matching lines. Not imported is the submitted row count minus that, plus any rows the template rejected. The monitor then links each journal back to its CSV Task through a header field, set with record.submitFields() after the import. Mapping that link in the import itself failed to resolve the reference to the custom record, so the post-import link was the reliable path.

Three bugs that returned zero, silently

After go-live, reconciliation reported 0 imported for tasks whose journals were plainly in the system. There was no error anywhere. It took three fixes to get a real count, and all three had the same shape: an exception inside a try/catch that returned zero.

Bug 1: two formats for the same ID. The template wrote the CSV Job ID as the bare task ID, like 4812. The monitor searched for CSVTASK-4812. Two helpers built "the same" ID and had drifted apart. The search was valid. It just never matched. The fix was one format, with a comment on both sides saying they must match.

Bug 2: anyof with a bare value. The fallback searches used filters like ['type', 'anyof', 'Journal']. anyof expects an array, so search.create() threw "invalid operator, or is not in proper syntax", and the catch returned 0. Six filters had the same mistake.

;['type', 'anyof', 'Journal'] // throws, swallowed
;['type', 'anyof', ['Journal']] // works

Bug 3: dates as ISO strings. With anyof fixed, the searches failed again, now with "An unexpected SuiteScript error has occurred", thrown from run(). The trandate filter got a raw '2026-08-14', and the lastmodifieddate cutoff got toISOString(). Date filters need a Date or a value in the account's format:

filters.push(['lastmodifieddate', 'after', format.format({ value: cutoff, type: format.Type.DATETIME })])
filters.push(['trandate', 'on', format.format({ value: tranDate, type: format.Type.DATE })])

The lastmodifieddate filter was in every fallback search, so fixing trandate alone changed nothing.

Each bug hid behind the one before it, because every search error became "0 imported" with no message. The lasting fix: search helpers now return the error with the count, and the monitor writes it into the CSV Task message when it can't reconcile.

With the searches fixed, one more problem showed up: right after COMPLETE, the new line field isn't always searchable yet. The monitor now retries inside a fixed window of wall-clock time. I covered that in NetSuite CSV import reconciliation: why your retry window should be time-based.

Recovering stuck tasks

A task can get stuck in Processing if a run fails after setting the state but before saving a massaged file. The CSV Task user event handles this on edit: if a task is Processing or Queued, has a processor task ID, and has no massaged file and no import task, saving the record processes it again. "Open the task, Edit, Save" is a recovery step anyone in support can follow.

The same user event resubmits the monitor for tasks in Import Submitted with no counts yet, in case the monitor's self-chain stopped.

Testing

Both sides are mostly plain logic, so they test well:

• Gateway (Node.js): batch selection for each strategy, the grace period, .txt files, and the payroll file fallback
• NetSuite (Jest with mocked N/* modules): both templates, including missing columns and the balancing line, task states and progress, massaged file naming, import submission, the "already running" handoff, stuck-task recovery, and reconciliation

Conclusion

The two feeds looked different. ETL was "pick the right four files". Payroll was "turn a report into a journal". Most of what made them reliable was shared:

• Choose files on purpose, record how they were chosen, and skip files still being written
• One CSV Task per file, so one bad file fails alone and can be fixed alone
• Templates own the reshaping, and the massaged headers stay a stable contract with the import map
• Balance before import, with a visible balancing line instead of a rejected journal
• A marker column, because task.checkStatus() only tells you the job finished
• Errors on the record, because a silent zero costs more time than any real bug

If you build something similar, start with the marker column. Every other part of reconciliation gets simpler once you can find exactly the records one import created.