Preparing CSV Parser Bank Statement Files for Import

To import your CSV bank statement file successfully, make sure if follows the correct specifications and is UTF-8 encoded.

Convert your CSV file to UTF-8 using any of the following:

If you're using Excel, click Save As and select CSV UTF-8 from the list.
If you're using Notepad, click File > Save As, select UTF-8 from the Encoding dropdown, then click Save in the Save As window.

You can download a CSV template from the NetSuite File Cabinet or the Parser Configuration subtab of the Format Profile page, or create your own CSV file with the required columns and specifications.

Note:

Your CSV file can have transactions for one or more accounts.

Keep the following in mind when preparing your CSV column data:

Column Data	Important Things to Note
Account Number	The limit is 80 characters. Important: For compliance, you can't enter a credit card number. NetSuite removes all non-numeric characters to make sure the unique account ID doesn't contain a credit card number, If the remaining string is less than 13 or more than 20 characters, NetSuite accepts the unique account ID. If the string is between 13 and 20 characters, NetSuite runs a Luhn check. If it passes, you can't use that account ID. If it fails, NetSuite accepts it.
Date	This is the transaction date. Use any of these supported date formats: 2 – 3 M (month), 2 D (day), and 2 or 4 Y (year) characters with no separators Example: MMDDYY, DDMMYYYY, or MMMDDYYYY 1 D (day) or 1 M (month) character but must include a separator Example: M-D-YYYY, YYYY-MM-D, or M-DD-YYYY You can use hyphen (-), slash (/), period (.), or space as separators Use English month abbreviations Long date formats and 3–letter Month abbreviations are not supported. Example, Month D, YYYY, which interprets to March 1, 2021 or Mon D, YYYY, which interprets to Jan 1, 2021. The parser automatically removes anything before or after the date, like time or time zone. The SuiteApp removes them because only the transaction date is needed for bank reconciliation.
Amount	The SuiteApp supports CSV files with any of these Amount column setups: Single Amount column – Positive amounts are Credit, negative amounts are Debit. Negative entries must have a negative sign or be in parentheses. Single Amount column with a separate amount indicator – Amount entries shouldn't be signed or in parentheses. Two Amount columns (Credit and Debit) – Debit column entries can be unsigned. If your file shows transaction amounts as absolute values, do the following: Map the Amount Indicator field to mark entries as positive or negative. Then, set the Credit Indicator and Debit Indicator preferences under Formatting Preferences.
Credit Amount	Include this column if the transaction amount is an absolute value and you have separate credit and debit columns. The amount can also be a signed positive number, for example, +100.
Debit Amount	Include this column if the transaction amount is an absolute value and you have separate debit and credit columns. The amount can also be a signed negative value, for example, -100.
Amount Indicator	Include this column if the transaction amount is an absolute value.
Transaction Number	Unique transaction or reference number. You can also use the check number here.
Amount Currency	Make sure the transaction amount's currency matches the NetSuite GL account currency to avoid import errors. The system ignores any currency symbol before or after the transaction amount.
Memo	The limit is 4,000 characters. If your Memo column goes over the limit, the import still works, but the data is cut off at 4,000 characters.
Financial Institution ID	Unique number assigned to a financial institution transaction.
Customer Name	This is the NetSuite customer name.
Customer ID	This is the NetSuite internal customer ID.
Invoice Numbers	If a transaction has more than one invoice number, separate them with a comma.
Account Closing Balance	The system recognizes the last transaction line for each unique bank and credit card account.
Closing Balance Date	The system recognizes the last transaction line for each unique bank and credit card account.

Column Data

Important Things to Note

Account Number

The limit is 80 characters.

Important:

For compliance, you can't enter a credit card number. NetSuite removes all non-numeric characters to make sure the unique account ID doesn't contain a credit card number,

If the remaining string is less than 13 or more than 20 characters, NetSuite accepts the unique account ID.
If the string is between 13 and 20 characters, NetSuite runs a Luhn check. If it passes, you can't use that account ID. If it fails, NetSuite accepts it.

Date

This is the transaction date. Use any of these supported date formats:
- 2 – 3 M (month), 2 D (day), and 2 or 4 Y (year) characters with no separators
  
  Example: MMDDYY, DDMMYYYY, or MMMDDYYYY
- 1 D (day) or 1 M (month) character but must include a separator
  
  Example: M-D-YYYY, YYYY-MM-D, or M-DD-YYYY
- You can use hyphen (-), slash (/), period (.), or space as separators
- Use English month abbreviations
Long date formats and 3–letter Month abbreviations are not supported.

Example, Month D, YYYY, which interprets to March 1, 2021 or Mon D, YYYY, which interprets to Jan 1, 2021.
The parser automatically removes anything before or after the date, like time or time zone.

The SuiteApp removes them because only the transaction date is needed for bank reconciliation.

Amount

The SuiteApp supports CSV files with any of these Amount column setups:
- Single Amount column – Positive amounts are Credit, negative amounts are Debit. Negative entries must have a negative sign or be in parentheses.
- Single Amount column with a separate amount indicator – Amount entries shouldn't be signed or in parentheses.
- Two Amount columns (Credit and Debit) – Debit column entries can be unsigned.
If your file shows transaction amounts as absolute values, do the following:
1. Map the Amount Indicator field to mark entries as positive or negative.
2. Then, set the Credit Indicator and Debit Indicator preferences under Formatting Preferences.

Credit Amount

Include this column if the transaction amount is an absolute value and you have separate credit and debit columns. The amount can also be a signed positive number, for example, +100.

Debit Amount

Include this column if the transaction amount is an absolute value and you have separate debit and credit columns. The amount can also be a signed negative value, for example, -100.

Amount Indicator

Include this column if the transaction amount is an absolute value.

Transaction Number

Unique transaction or reference number. You can also use the check number here.

Amount Currency

Make sure the transaction amount's currency matches the NetSuite GL account currency to avoid import errors.
The system ignores any currency symbol before or after the transaction amount.

Memo

The limit is 4,000 characters.

If your Memo column goes over the limit, the import still works, but the data is cut off at 4,000 characters.

Financial Institution ID

Unique number assigned to a financial institution transaction.

Customer Name

This is the NetSuite customer name.

Customer ID

This is the NetSuite internal customer ID.

Invoice Numbers

If a transaction has more than one invoice number, separate them with a comma.

Account Closing Balance

The system recognizes the last transaction line for each unique bank and credit card account.

Closing Balance Date

The system recognizes the last transaction line for each unique bank and credit card account.

Remove these:

Empty columns (with no data) between filled columns
Empty rows (with no data) between filled rows
Any columns not listed in the template or CSV Parser Configuration mapping fields

The system automatically ignores any account summary before the column title headers during import.

Related Topics