Import Wells Fargo Bank Statement to Bank Reconciliation

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 (DED). Unfortunately, the Wells Fargo transaction export file presented some challenges.

File contains amounts in 2 columns, need one to be blank

When opened in Notepad, the file contains 2 columns for the amounts. One for the Debit Amounts, and the other for the Credit Amounts. Both columns contain values for each transaction. One of the column values is 0.00, while the other column contains the transaction value. So that the DED will work correctly when importing the bank statement to the bank reconciliation, replace all ,0.00 in the export file with a comma and blank.

Use Notepad and Edit->Replace. In Find type a comma then 0.00 (,0.00). In Replace type a comma then a space (, ). Replace all.

Assigning 2 columns to Amount in Field Mapping

In Column Definitions, only one amount definition is found. To modify the Column Definition to recognize 2 columns for the amounts, add Column 14 for Credits. To save changes, length must be added to each column definition. When Assisted Setup creates definitions, it does not assign a length to all the columns, but saving as a user, length is required.

Mapping 2 columns to one field in BC

Field Mapping needs to be updated to accept an amount from either of the amount columns into the one field in BC.

1. On Column 13, select Optional (only one field will contain a value on each line). Set the Multiplier to negative one (-1.00) to change the sign of the amount coming in from the bank file. Select Overwrite Value so that if Column 14 contains the amount, it will overwrite the empty value in Column 13.
2. Add Column 14, select Field ID 7 (Statement Amount). Select Optional (only one field will contain a value on each line).

Once these changes are complete, test.

Summary

A Data Exchange Definition can be used to successfully import Wells Fargo transaction activity to a Bank Reconciliation or to the Payment Reconciliation Journal. If you follow the steps above to modify the DED created by Assisted Setup and run Replace from Notepad for each file, you can use the Import Bank Statement action allowing you to efficiently reconcile your bank account to BC. I hope you agree that this is The Righter Way to automate Wells Fargo reconciliations in Microsoft Dynamics 365 Business Central.