Preparing CSV Parser Bank Statement Files for Import

To ensure a successful CSV bank statement file import, your file must adhere to the correct specifications and must be UTF-8 encoded.

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

If you are using Excel, convert the file by clicking Save As and then select CSV UTF-8 from the list.
If you are using Notepad, convert the file by clicking File > Save As and then select UTF-8 from the Encoding dropdown before clicking Save on the Save As popup window.

The SuiteApp provides a CSV template that you can download from the NetSuite File Cabinet or the Parser Configuration subtab of the Format Profile page. Alternatively, you can also create your CSV file with the required columns and specifications.

Note:

The CSV file can contain transactions that belong to a single or multiple account.

Take note of the following information when preparing your CSV column data:

Column Data	Important Things to Note
Account Number	There is a limit of 80 characters. Important: For compliance reasons, you cannot enter a credit card number. To ensure that the unique account ID does not contain a credit card number, NetSuite removes all non-numeric characters. If the remaining string is less than 13 characters or greater than 20 characters, NetSuite accepts the unique account ID. If the remaining string is greater than 12 characters and less than 21 characters, NetSuite performs a Luhn check. If the check passes, NetSuite blocks you from using the unique account ID. If the check fails, NetSuite accepts the unique account ID.
Date	Refers to the transaction date. Content must follow any of following 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 For separators, only hyphen (-), slash (/), period (.), and space are supported Use English month abbreviations only Long date format and format with 3–character 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. Other characters before or after the date value, for example time and time zone, are automatically removed during parsing. The SuiteApp removes them because only the transaction date is needed for bank reconciliation.
Amount	The SuiteApp supports CSV files that contain any of the following Amount column data: Single Amount column – Positive amounts are recognized as Credit, while negative amounts are recognized as Debit. Negative amount entries must have a negative sign or enclosed in parentheses. Single Amount column but with separate amount indicator – Amount entry must not be signed or enclosed in parentheses. Two Amount columns (Credit and Debit) – Amount entries in the Debit column can be unsigned. If the transaction amount in your file displays as an absolute value, perform the following actions: Map the Amount Indicator field to mark a positive or negative entry. Then, set the Credit Indicator and Debit Indicator preferences under the Formatting Preferences subtab.
Credit Amount	Include this column data if the transaction amount displays as an absolute value and you have a separate column for debit and credit amount entry. Amount entry can also be a signed positive number. Example, +100.
Debit Amount	Include this column data if the transaction amount displays as an absolute value and you have a separate column for debit and credit amount entry. Amount entry can also be a signed negative value. Example, -100.
Amount Indicator	Include this column data if the transaction amount displays as an absolute value.
Transaction Number	Unique transaction or reference number. This can also be populated with the check number.
Amount Currency	Make sure the transaction amount's currency matches the NetSuite GL account currency to avoid data import errors. The system ignores any Currency symbol appended before or after the transaction amount.
Memo	There is a limit of 4,000 characters. If the value in your Memo column exceeds the character limit, import will still be successful. However, the parsed data is truncated at 4,000 characters.
Financial Institution ID	Unique identifying number assigned to a financial institution transaction.
Customer Name	Refers to the NetSuite customer name.
Customer ID	Refers to the NetSuite internal customer ID.
Invoice Numbers	If a transaction line contains more than one invoice number, separate each invoice number using 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

There is a limit of 80 characters.

Important:

For compliance reasons, you cannot enter a credit card number. To ensure that the unique account ID does not contain a credit card number, NetSuite removes all non-numeric characters.

If the remaining string is less than 13 characters or greater than 20 characters, NetSuite accepts the unique account ID.
If the remaining string is greater than 12 characters and less than 21 characters, NetSuite performs a Luhn check. If the check passes, NetSuite blocks you from using the unique account ID. If the check fails, NetSuite accepts the unique account ID.

Date

Refers to the transaction date. Content must follow any of following 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
- For separators, only hyphen (-), slash (/), period (.), and space are supported
- Use English month abbreviations only
Long date format and format with 3–character 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.
Other characters before or after the date value, for example time and time zone, are automatically removed during parsing.

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

Amount

The SuiteApp supports CSV files that contain any of the following Amount column data:
- Single Amount column – Positive amounts are recognized as Credit, while negative amounts are recognized as Debit. Negative amount entries must have a negative sign or enclosed in parentheses.
- Single Amount column but with separate amount indicator – Amount entry must not be signed or enclosed in parentheses.
- Two Amount columns (Credit and Debit) – Amount entries in the Debit column can be unsigned.
If the transaction amount in your file displays as an absolute value, perform the following actions:
1. Map the Amount Indicator field to mark a positive or negative entry.
2. Then, set the Credit Indicator and Debit Indicator preferences under the Formatting Preferences subtab.

Credit Amount

Include this column data if the transaction amount displays as an absolute value and you have a separate column for debit and credit amount entry. Amount entry can also be a signed positive number. Example, +100.

Debit Amount

Include this column data if the transaction amount displays as an absolute value and you have a separate column for debit and credit amount entry. Amount entry can also be a signed negative value. Example, -100.

Amount Indicator

Include this column data if the transaction amount displays as an absolute value.

Transaction Number

Unique transaction or reference number. This can also be populated with the check number.

Amount Currency

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

Memo

There is a limit of 4,000 characters.

If the value in your Memo column exceeds the character limit, import will still be successful. However, the parsed data is truncated at 4,000 characters.

Financial Institution ID

Unique identifying number assigned to a financial institution transaction.

Customer Name

Refers to the NetSuite customer name.

Customer ID

Refers to the NetSuite internal customer ID.

Invoice Numbers

If a transaction line contains more than one invoice number, separate each invoice number using 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 the following:

Empty columns (containing no data) between other filled columns
Empty rows (containing no data) between other filled rows
Any columns not specified by the template or CSV Parser Configuration mapping fields

Any account summary located before the column title headers are automatically ignored by the system during import.

Related Topics