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

[Blog bot]

Источник: https://stoneridgesoftware.com/how-t...-dynamics-nav/
==============

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



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.



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 = [Some examples: Date, Text, Decimal, etc.]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”



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



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.



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 =



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.



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



Источник: https://stoneridgesoftware.com/how-t...-dynamics-nav/