Skip to main content

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

by Cynthia Priebe - Microsoft MVP
Consultant and Managing Partner, MGC Group LLC

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

bc-ded-tdbank-1.png

bc-ded-tdbank-2.png

Bank #2 Example

bc-ded-tdbank-4.png

Downloaded file

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

bc-ded-tdbank-5.png

Or this.

bc-ded-tdbank-6.png

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.

bc-ded-tdbank-7.png

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

bc-ded-tdbank-8.png

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.)

bc-ded-tdbank-9.png

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.

bc-ded-tdbank-10.png

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.

bc-ded-tdbank-11.png

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

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com 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

About Cynthia Priebe - Microsoft MVP

Cynthia is a Microsoft MVP, consultant and owner of MGC Group, LLC where she focuses on Sharing the Righter WayTM of working with and improving 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 solve problems, enhance productivity, and find the best of the many ways to work in Business Central. 

She is a regular presenter at industry conferences including DirectionsNA, DynamicsCon and Community Summit. On the original team to write the MB-800 Micrsoft Dynamics 365 Business Central Functional Consultant certification exam, a member of the DUG Mentorship Meetup Group, on the Programming Committee for Community Summit 2024, a Microsoft Certified Trainer, and a certified Microsoft Dynamics 365 Business Central Functional Consultant, Cynthia enjoys giving back to the community in thanks for all it has given to her.

You can reach Cynthia through LinkedIn

In addition to contributing her ...

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com 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