7 Steps to Import Bank Statement in XLS File Dynamics NAV / 365 BC

It was an easy solution by Dynamics NAV to import bank statement on XLS file until I understood the configuration needed to implement this solution.

The default way in Dynamics NAV is to import Bank Statement in XML format, but it’s just needed the following steps to make this happen in XLS file format:

1. Data Exchange Definitions
2. Data Exchange Definitions – Line Definitions Tab
3. Data Exchange Definitions – Column Definitions Tab
4. Field Mapping – General Tab
5. Field Mapping – Field Mapping Tab
6. Bank Export/Import Setup
7. Bank Accounts

1. Data Exchange Definitions
   1. Go to the Data Exchange Definitions page.
   2. Define multiple mappings when importing or exporting files.
   3. Create a new Data Exchange Definition for Bank Statement Import.
   4. Enter Code and Name to define the new definition.
   5. Setups required:

File Type= Variable Text
Type= Bank Statement Import
File Encoding = WINDOWS
Column Separator = Comma
Click “Show more fields”
Reading/Writing XMLport = 1220
Ext. Data Handling Codeunit = 1240

2. Data Exchange Definitions – Line Definitions Tab

1. In the Line Definitions, keep the Line Type, Code, and Name fields as it is.
2. Enter the correct value (4) for Column Count that matches the number of
   columns in the CSV file that will be imported. In this case, we will define in the CSV file 4 columns (Date, Description, Debit Amount, Credit Amount)

3. Data Exchange Definitions – Column Definitions Tab

In this step, you will map the Fields of Table Bank with the corresponding columns in CSV File format.

1. Column No. = Refer to the fist column in CSV File (1,2,3,4).
2. Name = Pick Name for the columns in CSV File (Date, Description, Debit, Credit).
3. Data Type = Select the appropriate data format for each field (Numeric Field = Decimals) otherwise will be (Text)
4. Data Formatting = Data types of date and decimal, enter “en-US”.

4. Field Mapping – General Tab

We will map the created columns to the Bank Reconciliation table columns in Dynamics NAV.

1. Go to Line Definition Fast Tab again, and Click on Field Mapping.
2. From the toolbar, click New.
3. Enter the following setups:

Table= 274
Name = Bank Account Reconciliation XLS
Mapping Codeunit = 1248

5. Field Mapping – Field Mapping Tab

1. Go down to Field Mapping tab.
2. Map columns to the correct table columns.
   1. Select Field ID Column No for every line.
   2. Find the corresponding column in the Bank Acct. Reconciliation Line table.
   3. Repeat the last steps for all columns.
   4. Mark “Optional” for Debit Amount & Credit Amount Columns. This option used to validate the Blank Data, so the mapping process will be skipped if the field is empty.

6. Bank Import/ Export Setup

1. Go to Bank Export/Import Setup.
2. Enter Code and Name.
3. Direction = Import
4. Processing Codeunit Id= 1270
5. Data Exchange Definition Code = The name of the Data Exchange Definition.

6. Bank Account Card

Here we will assign the Bank Import Setup code in Bank Account Card.

1. From Transfer Tab, Field “Bank Statment Import”
2. Select your bank statement import code.

7. Bank Account Reconciliation

1. Go to Bank Account Reconciliations & click New on the toolbar.
2. Select the Bank Account.
3. Click the Import Bank Statement button from the toolbar.
4. Select the CSV of the statement.
5. See your data result imported.

Enjoy the Process.