How to import open bank transactions to NetSuite

Introduction

Importing open bank transactions is one of the final steps in a NetSuite data migration and one that is easy to underestimate.

If this step is skipped or handled incorrectly, the first bank reconciliation in NetSuite might not tie out, even though the NetSuite and legacy GL bank balance tie.

This article walks through how to prepare and upload open bank transactions during a NetSuite migration using a net change approach. Most of the examples use QuickBooks Online. However, the same concepts apply regardless of your legacy system.

If you are migrating detailed historical transactions, this step is not required. See my article to learn more about how OptimalData can load your detailed transaction history.

When Is This Step Required?

You only need to import open bank transactions if:

• You migrated balances using a net change or summary trial balance approach

The goal is simple:

NetSuite must reflect the same outstanding bank transactions as existed in the legacy system at cutover. However, these transactions must have a zero-GL impact. The original GL impact of the open bank transactions was posted through the historical financials loaded as journal entries. This article discusses the steps to complete this step successfully.

What Gets Imported?

Users must load two sets of transactions:

• Uncleared bank transactions from the most recent completed bank reconciliation

• New cash transactions dated after that reconciliation and before go-live

These transactions allow NetSuite to reconcile cleanly in month one.

Prerequisites Before You Start

Complete these steps in the legacy system before preparing your files:

1. Complete the final bank reconciliation in the legacy system

   This reduces the volume of transactions you need to import. It will be much easier to reconcile these accounts in the legacy system than in NetSuite. If you are behind on completing your bank recs, I’d recommend starting this process early.

2. Clean up old uncleared items

   Void or reissue stale checks and deposits. It is much easier to do this in QuickBooks than after go-live.

3. Create an OPENBANK suspense account in NetSuite

   • Account name: OPENBANK

   • Account type: Expense

   • Purpose: Offset the GL impact of imported open bank transactions

Once these steps are complete, you are ready to prepare the data.

The Steps for Importing Open Bank Transactions

Step 1: Extract Uncleared Transactions from the Last Reconciliation

See below for system-specific steps:

QuickBooks Online

In QuickBooks Online:

1. Navigate to the Bank Reconciliation Report for the last completed month

   Refer to the official Intuit reconciliation guide as needed.

2. Scroll to:

   • Uncleared checks and payments

   • Uncleared deposits and other credits

QuickBooks Online does not allow this report to be exported to Excel. However, if you highlight the Date column and all transaction fields, you can copy and paste the data into Excel.

I recommend pasting both sections into a single worksheet.

QuickBooks Desktop

In QuickBooks Desktop, navigate to Reports → Banking → Previous Reconciliation.

• Select the applicable account in the “Account” dropdown

  • You only need to complete bank reconciliations for accounts with a non-zero balance at the go-live date.

• Select the most recent date in the “Statement Ending Date” box

• Select “Detail” in the “Type of Report” section

• Select “Transactions cleared plus any changes…” in the “In this report, include” section

• Click the blue “Display” button

• Click the “Excel” button to export the report

Sage Intacct

In Sage Intacct, follow these steps:

• Click Applications → Cash Management → View reconciliation histories

• Click the “Reconciliation Report” for the desired bank reconciliation

• Click “Excel” to export to Excel

Step 2: Add Current-Month Bank Activity

Next, capture new transactions posted after the reconciliation date:

1. Navigate to the Balance Sheet or General Ledger report

2. Drill into the bank account

3. Export the transaction detail to Excel

From this export:

• Keep only transactions dated after the reconciliation date

• Exclude anything already listed as uncleared

If the last bank reconciliation date in the legacy system matches your NetSuite go-live date, this step is not necessary.

Finally, combine these transactions with the uncleared items into one consolidated spreadsheet.

Step 3: Map the Data to NetSuite

Before creating CSV upload files, the transactions must be mapped correctly.

Transaction Type

Not all transaction types can be imported directly into NetSuite. Unsupported types are typically loaded as journal entries. The purpose of these transactions is simply to mark them as cleared once the bank account is debited.

Each unique NetSuite transaction type requires its own CSV template.

Payee or Customer

• Required for checks, deposits, and certain cash transactions

• Not required for journal entries

• The entity must exist in both systems

GL Accounts

Each transaction will impact only two accounts:

• The bank account

• The OPENBANK suspense account

Step 4: Upload Transactions into NetSuite

Prepare and upload one CSV file per NetSuite transaction type using your implementation team’s templates.

If your internal team lacks bandwidth, this is a common area where migration specialists add value.

Step 5: Reverse the OPENBANK Impact

After the import, the bank balance in NetSuite will be temporarily incorrect.

Example:

• Bank balance on 4/30: $50

• Imported open transactions: $15

• New NetSuite bank balance: $65

• OPENBANK balance: ($15)

Post a reversing journal entry:

• Debit OPENBANK $15

• Credit Bank Account $15

This restores the correct cash balance while preserving outstanding activity.

Step 6: Complete the First Bank Reconciliation

When performing the first reconciliation in NetSuite:

• Select all monthly financial journal entries

• Include the OPENBANK reversing journal entry

The cleared balance should match the final reconciliation balance from the legacy system.

Final Tips and Common Pitfalls

• Post all open transactions to the same posting period

  This keeps OPENBANK at zero and avoids issues.

• OPENBANK should always net to zero

  If it does not, someone likely voided or reissued a transaction using the wrong GL account.

• Deactivate OPENBANK after reconciliation

  This prevents future misuse.

• Multiple bank accounts

  Include all transactions in one file and add a bank account column for mapping.

What if you need to void a payment imported during this process?

If you need to void a payment, you should reverse the GL impact with a journal entry using the legacy system’s original GL impact. You should not use the OPENBANK GL account.

• Post a journal entry to DR Cash for $100 and CR Accounts Payable for $100

• Post a new vendor payment against the $100 credit balance created above

If you change the OPENBANK GL account, you will create a variance that can roll into retained earnings and cause confusion.

Need Help with Your NetSuite Data Migration?

If your team is handling a NetSuite implementation and needs help executing a clean, audit-ready data migration, OptimalData Consulting specializes in this exact work.

We design and execute custom data migration strategies that help finance teams go live cleanly and reconcile with confidence.