Import your TD Bank Statement to Bank Reconciliation in Microsoft Dynamics 365 Business Central with a Data Exchange Definition

February 3 2023

Listen to article:

FREE Membership Required to View Full Content:

Become a Member Login
Joining gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates. 
Learn more about us here

Microsoft Dynamics 365 Business Central (BC) Data Exchange Definitions are used to export or import data between BC and an outside source. One of the most popular uses is importing a bank statement into BC for use in a bank reconciliation. In most cases, extracting your data from the bank and using it with Assisted Setup’s Set up bank statement file import format works without changes needed to the Data Exchange Definition. As one unlucky user found, it does not work so well with the exported bank transactions from TD Bank. If you also use TD Bank, don’t despair. Let me show you how I got it to work for this user.

Get the right data from the bank

When preparing to use either Set up bank statement file import format or manually create your Data Exchange Definition (DED), first make certain you are getting the correct data from the bank. Do NOT export a bank statement! An exported bank statement will not work. What we need is an export of bank transactions or bank activity. I know, assisted set up and the action on the Bank Reconciliation page use the word “statement,” but let me assure you using an exported statement will not work. Here are examples of what downloading bank “activity” looks like from other banks (not TD Bank).

Bank #1 Example

Bank #2 Example

Downloaded file

When opened in Notepad, the resulting file should look something like this.

Or this.

The line separator and file encoding of the file are used in the DED. For transaction imports to be successful, it is important that these are not modified. These are highlighted in the file examples above (see the page footers).

Lesson #1: Line separator & file encoding may inadvertently be changed

Unfortunately, when re-saving the downloaded bank activity files after viewing in Excel or other file editors, the line separator and file encoding settings may be inadvertently changed. This can even happen in saving from Notepad. See the bottom of the save in Notepad page below.

If you get the following error trying to import transaction activity, the wrong DED format may be selected in the Bank Export/Import Setup.

If this is not the case, a difference between the DED and the type, encoding and/or line separator of the file may be the cause. This happened to us as we were testing the TD Bank .csv activity file after viewing and saving through Excel and/or Notepad++.

Opening in Notepad and saving as .csv with Encoding of UTF-8 will frequently resolve the error. If not, only then should you try changing the File Encoding on the DED from WINDOWS to UTF-8. (I didn’t need to do this, but a user in a different environment did.)

The TD Bank activity file

The bank activity file we exported from TD Bank worked to successfully create the DED when using Set up bank statement file import format from Assisted Setup. Or so we thought.

Lesson #2 - TD Bank activity file has 2 amount fields

The TD Bank activity file contains two fields with amounts, one for a debit amount and the other for a credit amount. When Assisted Setup creates the DED, only one amount field can be selected, and Debit was picked up for the definition.

On testing, records with values in the Credit field were imported, however 0.00 was used for the amount in the bank or payment reconciliation. When we saw this result, we knew that we needed to find a way to get the amount from either the Debit or the Credit field to BC. We needed 2 column definitions mapped to only one field in BC.

Adding a second amount column to the Data Exchange Definition

About Cynthia Priebe

Cynthia is a Senior Trainer and Consultant with New View Strategies, the go-to source for actionable training and process efficiency in Microsoft Dynamics 365 Business Central. Cynthia is recognized for her ability to look at operational and reporting challenges with a fresh take and propose solutions using core features and functionality. Focused for close to 20 years on Dynamics NAV and now D365 Business Central, she adores helping clients and partners with solving problems, enhancing productivity, and finding The Righter WayTM to work in Business Central. She is a regular presenter at industry conferences, on the original team to write the MB-800: Microsoft Dynamics 365 Business Central Functional Consultant certification exam, a Microsoft Certified Trainer, and a certified Microsoft Dynamics 365 Business Central Functional Consultant. You can reach Cynthia on LinkedIn.

At New View Strategies, all our experts have “been there, done that” – we are experienced, former users who have walked in your shoes. We  work with frustrated Business Central/NAV users who struggle to use their system and find the answers they need. We teach them to truly understand how the system can support their business processes to make immediate and lasting improvements, and as a result get more done in less time, make better business decisions, and even love their system. In addition to helping end users, New View Strategies offers Consultant Accelerator programs to help get new-to-BC consultants up, running, and billing as soon as possible. 

We can help, drive real change via a custom class delivered remotely or at your site, or choose from over 40 online and in-person classes at

More about Cynthia Priebe