Skip to main content

Additional Field to Extend the Bank Statement with Additional Data from CSV or CAMT files

Introduction

The ZoneReconcile Suiteapp allows users who utilize files in the format of CSV, CAMT053 or CAMT054 to extend the standard parsed data and add additional column on their bank statements in order to load also e.g. payment references, end to end IDs etc., that can be used for matching with transactions in the account. This option is only possible with the mentioned file formats, therefore we recommend to use these formats when doing the reconciliation.

CSV

Extend the CSV format is really simple - user shall simply map/define the column to be loaded. In the CSV configuration section of the configuration record we have certain predefined fields that we allow the user to map, so to add an additional field, please navigate to Additional Parameters & Fields section of the Bank Account Configuration:

zone_rec_bank_account_config_additional_fields_csv_camt.png

Click on the button New Bank Statement Additional Fields, which opens the following page:

  1. Set the custom form to Default form as on the screenshot
  2. Input the number/position of the column in the CSV file that you want to be loaded, e.g. the Amount column has position “3” and you want to load the column right after Amount, so you input “4”.
  3. In the Bank Statement Transaction field you need to state a field/column, where this data will be loaded into.
  4. In the copy settings section, either mark the preference “Do not copy to the transaction” or input a NetSuite Field where the data will be copied from the statement to the transaction.

zone_rec_bank_statement_additional_fields.png

 

CAMT053/CAMT054

The CAMT files follow the same steps as for the CSV, with one difference - instead of the CSV column the user has to define the XML XPATH that leads to the values that shall be parsed from the file. The XML XPATH looks for example like this:

/*[local-name()='Document']/*[local-name()='BkToCstmrStmt']/*[local-name()='Stmt']/*[local-name()='Ntry']/*[local-name() = 'NtryDtls']/*[local-name()='TxDtls']/*[local-name()='RtrInf']/*[local-name()='Rsn']/*[local-name()='Cd']

The code defines the structure of the XML file from where the additional data shall be sourced from. Practically the hierarchy (the XML tree) looks like this:

/*[local-name()='Document']
/*[local-name()='BkToCstmrStmt']
/*[local-name()='Stmt']
/*[local-name()='Ntry']
/*[local-name()='NtryDtls']
/*[local-name()='TxDtls']
/*[local-name()='RtrInf']
/*[local-name()='Rsn']
/*[local-name()='Cd']

To get the structure/tree, you may use an online XML Parser, such as this one.

Please make sure the XML Xpath does not contain any additional space between the apostrophes (''), in between the tags and at the end of the path.

After saving the record, you should see a result similar to this:

zone_rec_bank_statement_additional_fields_example.png

 

Such a setting would mean that the user extended the bank statement with additional data that are sourced from the CAMT file based on the defined XML XPATH and are loaded into the End-To-End-ID field/column on the statement and the values are copied to the transaction to the Memo field. Should you need to add another additional field from your CAMT or CSV file, then please refer to the previous article how to add another column into a bank statement line.

Note that if you have multiple CAMT configurations having the same Multiple Statement Identifier (for example when statements should be further recognized by currency), you will need to add the same mapping in all of these configurations.

 

Was this article helpful?