Bank Statement Import CSV
I have download the “BankStatementImportCSV,CSV” from the NetSuite and input the transaction row by row. But I still cannot import to NetSuite at all.
I have double read the NetSuite Help File every single post and still cannot find the solution. Anyone can help me.
At first glance, it appears you are missing the internal ID of these records. Without it, NetSuite can not match the record and would fail to import.
If it was a specific field causing the failure, it should give you “results” which you can download and see the specific errors.
Thanks for the reply, Are you refer to the NS Customer Internal ID? It is optional.
- Date (MM/DD/YYYY) – The transaction date. Content must be in the format of MM/DD/YYYY. If you are using Excel, ensure that the date does not get reformatted.
- Payer/Payee Name – Payer or Payee name. There is a limit of 70 characters.
- Transaction ID – Unique Bank Transaction or Reference ID. This can also be populated with the check number. There is a limit of 90 characters.
- Transaction Type – Values in this column must be one of the following transaction types, all uppercase: CREDIT, DEBIT, CHECK, PAYMENT, ACH, INTEREST, DEPOSIT, TRANSFER, FEE, OTHER.
- Amount – The amount column must be a single column. If your file contains CREDIT and DEBIT columns, combine them into a single amount column. Positive amounts are CREDITS. Negative amounts are DEBITS, and must begin with a minus sign or be in parentheses.
The amount column must not contain currency symbols or commas instead of decimal places. The amount currency should be in the same currency as the selected account.
- Memo (Optional) – There is a limit of 4000 characters for the memo. The Memo field must not include any commas, semicolons or underscores.
- NS Internal Customer ID – (Optional) The NetSuite internal customer ID. Content must be numeric. There is a limit of 12 characters.
When you import the statement, NetSuite uses the customer ID first to try to match the statement to a NetSuite customer. If the customer ID is missing, NetSuite checks for a customer name next.
- NS Customer Name – (Optional) If the specified customer ID does not match any data, NetSuite uses the value to look up the customer ID. Content is free-form text. There is a limit of 512 characters.
When you import the statement, if the customer ID is missing, NetSuite uses the customer name to try to match the statement to a NetSuite customer. If the customer name is missing, NetSuite checks for invoice numbers.
- Invoice Number(s) – (Optional) A comma-separated list of invoice numbers in a single field, with no spaces between numbers (for example, 132,133,134). If you use a script or comma-separated file instead of the CSV template in a spreadsheet, put quotation marks around multiple invoice numbers (for example, “132,133,134”). For each invoice number in the list, there is a limit of 128 characters. If the imported file specifies multiple invoices for a transaction, they must all belong to same customer and AR account.
When you import the statement, if the customer name and customer ID are both missing, NetSuite uses the invoice numbers to try to match the statement to a NetSuite customer. If the customer name, ID, and invoice numbers are all missing, the system cannot perform an exact match. However, NetSuite can match the statement to a customer in other ways. See Customer Match Criteria.
I’m referring to the Internal ID that is automatically assigned to every record (transaction, item, customer, etc.)
This could be cross-referenced by doing a Transaction Saved Search that includes only deposits and payments (Field: Type)
As long as your saved search includes the Internal ID and Document Number, you could, in Excel or Google Sheets, do a VLOOKUP to match the Document Number to the Internal ID. Then you should be able to import the list using that ID as your key field. Without that field, I don’t know if NetSuite Import is smart enough to match the records.
Hope that makes sense (and works for you!).