How to Import a Bank Statement File for Bank Reconciliation in Dynamics NAV

By Jack Ries | January 16, 2017

How to import a bank statement file for bank reconciliation in dynamics nav

*This post was written for Dynamics NAV but the content directly applies to Dynamics 365 Business Central, which is the cloud product based on Dynamics NAV. You may notice some slight differences in the screenshots, but the information and steps are directly applicable to Dynamics 365 Business Central.

Bank Reconciliation is an important step in ensuring that your General Ledger and associated accounts are accurate, configured properly, and up-to-date. It can be tedious to manage if done manually. However, today I’ll show you how to import a CSV file from the bank into Dynamics NAV for easy comparison between the bank statement and your company’s accounts.

1. Data Exchange Definitions

First, go to the Data Exchange Definitions page. Here, you can define multiple mappings when importing or exporting files. We’ll create a new Data Exchange Definition for our Bank Statement Import. Click New on the toolbar.

Enter in Code and Name to define your new definition.

Here are some of the other setups you will need:

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

Bank Statement_Jack

2. Data Exchange Definitions – Line Definitions Tab

In the Line Definitions tab below, keep the Line Type, Code, and Name fields. Enter the correct value for Column Count that matches the number of columns in the CSV you will be importing.

Bank Statement_Jack 2

3. Data Exchange Definitions – Column Definitions Tab

In the Column Definitions tab, match each line you plan to import to a corresponding column in the CSV.
Column No. = Which column number in the CSV this line refers to
Name = Pick a name for the column in the CSV
Data Type = Data Format = You can leave this blank, but for data types like date, there may need to be formatting (e.g. dd/mm/yyyy)
Data Formatting = For data types of date and decimal, enter “en-US”

Bank Statement_Jack 3

4. Field Mapping – General Tab

Go back to the Line Definitions tab, and click Field Mapping. Click the New button on the toolbar. We will be mapping our created columns to the correct columns in NAV. To do that we need to tell it which table we’ll be comparing our import to.

Table= 274
Name = Jack CSV
Mapping Codeunit = 1248

Bank Statement_Jack 4

5. Field Mapping – Field Mapping Tab

Go down to the Field Mapping tab. Here, we will map our columns to the correct table columns. In the first line, select the first created column. Then, in the Field ID column, find the corresponding column in the Bank Acct. Reconciliation Line table. Do this for all of your columns.

Bank Statement_Jack 5

6. Bank Export/Import Setup

Next, go Bank Export/Import Setup and click New in the toolbar. Enter a Code and Name.
Direction = Import
Processing Codeunit Id= 1270
Data Exchange Definition Code = <The name of your Data Exchange Definition>

Bank Statement_Jack 6

7. Bank Accounts

Go to Bank Accounts and select the card of the Bank Account you would like to use. Under the Transfer tab, enter in your import name in the Bank Statement Import Format field.

Bank Statement_Jack 7

8. Bank Account Reconciliation Page

Go to Bank Account Reconciliations and click New on the toolbar. Select the Bank Account you’d like to use to open a new Bank Acc. Reconciliation window. Click the Import Bank Statement button on the toolbar and select the CSV of the statement you would like to import. In the Bank Statement Lines tab, you will see your data imported correctly into NAV for convenient bank account reconciliation matching.

NOTE:

Here is an example of a CSV format that will successfully import. Copy the table below, paste into Excel, and save it off as a CSV file. Be aware that there are no column headers:

11/23/2016 Ahornby Hvidevare A/S -85.73
11/23/2016 American Wood Exports -2977.19
11/23/2016 AR Day Property Management -139.56
11/23/2016 Aranteaux Aliments -2007.57
11/23/2016 Beekhuysen BV -770.21

Related Posts

Recommended Reading:

5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article
4.28.22 Dynamics GP

Uncommonly Used Features – Integrate Purchasing and Payables to Fixed Assets in Dynamics GP

Being able to integrate Purchasing and Payables to Fixed Assets in Dynamics GP will help you create visibility for your […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X