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

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

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

Get full access

or login