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

by | Updated December 20, 2018 | Dynamics 365 Business Central, Dynamics NAV, Financials

*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 = [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”

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

13 Comments

  1. Roshan

    Any mechanism to capture the statement ending balance to the bank statement header

  2. Taylor Valnes

    Hello Roshan,

    If you are attempting to fill in data on the bank statement header from a value that you are importing then I believe the answer is no, that it will still need to be filled in manually.

    Regards,
    Taylor

  3. abhi

    Hi Jack,

    I tried to configure bank reconciliation as per your blog and was able to do it straightforward. thaks for that.

    the only issue i am facing is that whenever I import the statement file the transaction month changes to 01/date/year. so for example if i have entered date as 03/18/2018,03/19/2018,03/20/2018 and when i import the date changes to 01/18/2018,01/19/2018 and so on. the date format is mm/dd/yyyy.

    Please guide me for this issue. i have tried all the possible option .

    Thanks

  4. Taylor Valnes

    Hello Abhi,

    I spoke with Jack and he hasn’t seen this issue before. The best route would be to open your statement in Excel, verify the dates are correct, and save it as a CSV. Once it’s saved as a CSV with the dates verified then you’ll want to try the import again.

    Regards,
    Taylor

  5. Frank

    Hi guys,

    I have a question for NAV2013, maybe it’st he same for 2017 but I don’t know:
    My bank is sending me csv files with an empty line at the end and NAV can’t import it, error like “it seems a line is missing in the file”.
    After a quick debug it seems the system is trying to catch this last line and expect it to be a real one with data but as it’s empty -> error.

    Did someone faced this situation ?
    I can’t find a way to setup the import tool to prevent reading an empty line.
    I tried to use the Footer parameter but I don’t know how to put in this the “CRLF” caption.

    Thanks a lot

  6. Michael Tweiten

    Hi Frank,

    I spoke with the author of this blog and this is what they suggested. Edit the CSV to remove the last line, or modify the codeunit code to either skip the last line or skip all lines that return an error.

    Regards,
    Michael

  7. Ian

    I have the same issue as Abhi

    On the Data Exchange Definition / Column Defs, I have

    Col No, Name, Data Type, Data Format, Data Formatting Culture
    1, Date, Date, dd/mm/yyyy, en-GB

    My example file to import is –

    01/01/2019,Order 106015,-36000.00
    01/01/2019,Order 106018,-7920.00
    27/01/2019,Invoice 108001,-24000.00
    01/02/2019,Order 106019,-5414.40
    01/02/2019,Order 106022,-8568.00
    28/02/2019,Invoice 108002,-720
    28/02/2019,Invoice 108005,-2400.00
    28/02/2019,Invoice 108006,-24000.00
    01/03/2019,Order 106020,-3628.80
    01/04/2019,Order 106021,-4608.00
    30/04/2019,Invoice 108003,-480
    01/05/2019,Order 106016,-50400.00
    31/05/2019,Invoice 108004,-1440.00
    31/05/2019,Invoice 108007,-720
    01/06/2019,Order 106017,-18000.00
    30/06/2019,Invoice 108008,-480

    The Bank Statement Lines when imported are all showing the month as January –

    Transaction Date Document No. Cheque No. Type Description Statement Amount Applied Amount Reconciled Difference Applied Entries
    01/01/2019 Bank Account Ledger Entry Order 106015 -36,000.00 No -36,000.00 0
    01/01/2019 Bank Account Ledger Entry Order 106018 -7,920.00 No -7,920.00 0
    27/01/2019 Bank Account Ledger Entry Invoice 108001 -24,000.00 No -24,000.00 0
    01/01/2019 Bank Account Ledger Entry Order 106019 -5,414.40 No -5,414.40 0
    01/01/2019 Bank Account Ledger Entry Order 106022 -8,568.00 No -8,568.00 0
    28/01/2019 Bank Account Ledger Entry Invoice 108002 -720.00 No -720.00 0
    28/01/2019 Bank Account Ledger Entry Invoice 108005 -2,400.00 No -2,400.00 0
    28/01/2019 Bank Account Ledger Entry Invoice 108006 -24,000.00 No -24,000.00 0
    01/01/2019 Bank Account Ledger Entry Order 106020 -3,628.80 No -3,628.80 0
    01/01/2019 Bank Account Ledger Entry Order 106021 -4,608.00 No -4,608.00 0
    30/01/2019 Bank Account Ledger Entry Invoice 108003 -480.00 No -480.00 0
    01/01/2019 Bank Account Ledger Entry Order 106016 -50,400.00 No -50,400.00 0
    31/01/2019 Bank Account Ledger Entry Invoice 108004 -1,440.00 No -1,440.00 0
    31/01/2019 Bank Account Ledger Entry Invoice 108007 -720.00 No -720.00 0
    01/01/2019 Bank Account Ledger Entry Order 106017 -18,000.00 No -18,000.00 0
    30/01/2019 Bank Account Ledger Entry Invoice 108008 -480.00 No -480.00 0

  8. Taylor Valnes

    Hello Ian,

    I spoke with Jack and he hasn’t seen this since Abhi’s comment, and there’s more going on than what we’re aware of that’s driving this. If the data you’re showing is from the excel file (presuming you tried what we proposed to Abhi), then this may require either reaching out to your partner or possibly using our support services. Please contact us if you’re furthering this discussion.

    Regards,
    Taylor

  9. Md Rabbi

    Regarding the problem, Ian mentioned: In the column definitions using this Data format, dd/M/yyyy worked out for us for a date like 31/12/2018

  10. Adam Williams

    Hi

    Thank you I found this post very helpful.

    I also am encountering the date format problem (e.g. 13/12/2018 in the .csv becomes 13/01/2018 when the statement is imported) [I am using GB date format].

    My solution was to simply put ‘d’ in for the Data Format in the hope that NAV would keep the day and fill in the month and year automatically. I cannot explain how or why but it seemed to work…..

    Thanks
    Adam

  11. Dalton Roll

    Hello Adam,

    Glad to hear the post was helpful! Thank you for sharing an example on how the Data Format field applies the formatting.

    Regards,
    Dalton

  12. Anthony Attard Montalto

    For date parsing issues where the month is evaluated to January or 01 regardless of the original date value the issue lies with the data formatting string that has been set in the Column Definitions fast-tab of the Data Exchange Definition Page. This happens when the text representing month is entered as “mm” (or “mmm”) instead of “MM” or (“MMM”). The TryEvaluateDate function in Codeunit 10 sets an EvalutedDate C/AL date variable with January as the month.

    Change your mm to MM in the Data Definition Definition Page !

  13. Andy Cashman

    Hi,
    just adding to Md Rabbi’s comment above, refer Theodore Gemmer’s post as below on 27 Jul 2016 at https://community.dynamics.com/nav/f/34/t/188295

    I have had this problem before of the date filling as January. Go to Column definitions for Data Exchange Definition. Make sure Data Format has an uppercase M.
    To explain, in my CSV file, date is formatted as 7/21/16… if I use data format of M/d/yy then the date will fill in correctly. If I use a data format of m/d/yy then the date will fill in wrong (January). Another example, if in my CSV file the date is shown as 21/7/16 (different culture) and I use data format of d/M/yy then date will fill in correctly. If I set data format as d/m/yy then it will fill in as January.

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

02sep12:00 pm12:30 pmBest Practices for Work Order Planning and Routing for Field Service Companies

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

09sep12:00 pm12:30 pmOverview of Microsoft Promotion for NAV and GP Clients

10sep11:00 am12:00 pmConfab with Stoneridge - Livestream - Field Service Comparisons

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

16sep12:00 pm12:30 pmHow to Upgrade to Dynamics 365 Finance and Operations: Examining Custom Code and ISV Implications

16sep2:00 pm2:30 pmScheduling and Dispatching for the Modern Field Service Company

23sep12:00 pm1:00 pmDynamics 365 Business Central Wave 2 Release Preview - An Overview of the Most Exciting New Features Coming in October

24sep11:00 am12:00 pmConfab with Stoneridge - Livestream - The Modern Workplace: Teams, Sharepoint, Outlook, Office, and AI Integration

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

30sep12:00 pm12:30 pmEmpowering Your Field Service Technicians with a Mobile Solution

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Orders

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X