How to define a CSV file to import bank statement information into Microsoft Dynamics 365 BC

Dynamics 365 Import

Microsoft Dynamics 365 Business Central allows you to easily reconcile your bank accounts by importing transactions from your bank.  In order to match the transactions,  you need to have the proper format for Dynamics 365.

Here’s how:

The file format just needs to be defined and assigned to the bank account. In this example, I will define a simple CSV file format with 4 fields:

Transaction Date, Description, Amount, Doc No

Dynamics 365 CSV Files

First, use the global search for ‘Data Exchange Definitions’

Click New and enter in a Code and name. Fill in the fields with the following information:

Dynamics 365 CSV Files

Then, scroll down to the Line Definitions section.

Provide the number of columns that will be in your CSV file (in this example, there are 4 fields).

Dynamics 365 CSV Files

Next, scroll down to the Column Definitions section and provide information about each field that will be in your CSV file:

The column ‘No’ will need to match the column number in your CSV.

Enter a name for each column. You will also need to select formatting information for Date or Decimal fields:

Dynamics 365 CSV Files

Scroll back up to the Line Definitions section and select Manage-Field Mapping.

Dynamics 365 CSV Files

Click New so you can map the fields from the CSV file to the fields in D365.

Dynamics 365 CSV Files

Fill in the following fields:

Dynamics 365 CSV Files

Mapping Fields

Continue down to the Field Mapping section and then select the first column on the first line.

Dynamics 365 CSV Files

Now, select the corresponding column in the Field ID column.

Dynamics 365 CSV Files

Complete this for all columns:

Dynamics 365 CSV Files

Now enter a global search for ‘Bank Export/Import Setup’.

Dynamics 365 CSV Files

Click New and enter a code/name. Fill in the following fields for the new line:

Dynamics 365 CSV Files

Scroll to the right and under Data Exch. Def. Code select the Data Exchange Definition that we just created in our previous steps:

Dynamics 365 CSV Files

Now go and assign this new Bank Statement Import Format to our bank account!

Go to Cash Management-Bank Accounts and select the bank account.

Then, expand the Transfer tab and select the Bank statement Import format that we just created:

Dynamics 365 CSV Files

You can now import this file during your bank account statement reconciliation.

  1. Enter a global search for ‘bank account reconciliation’
  2. Click New and select the bank account to reconcile
  3. Enter the statement ending date and statement ending balanceDynamics 365 CSV Files
  1. Next we will import our bank statement file to populate the left hand side of the window (Bank Statement Lines).

Please note – if the file has headers, remove them first. D365 will attempt to import the first line of data in the file.

  1. Select Bank-Import Bank Statement and select the CSV file to import.
    Dynamics 365 CSV Files

The transactions from our CSV file have now been imported and are visible on the Bank Statement Lines:

Dynamics 365 CSV Files

 

Matching Transactions

We can go to Matching/Match Automatically (leave transaction date tolerance blank).

Or we can select the line on the left and the line on the right and go to Matching/Match Manually if we wish to manually match

Dynamics 365 Business Central was able to automatically match the 3 imported transactions. The only transaction that is unmatched is Doc No G04005 (payment that was not in our CSV file).

Dynamics 365 CSV Files

The Balance now matches the Bank statement ending balance that we provided (18608.81).

We can preview our reconciliation by going to Actions-Posting-Test Report.

Dynamics 365 CSV Files

We are ready – now you can go to Process/Post and Print to post our bank account reconciliation

After posting, if we view our bank account it will show the Balance of $16,230.70. We can click that link and see the one outstanding check for Nod Publishers ($2378.11) is still marked as open.

Dynamics 365 CSV Files

Dynamics 365 CSV Files

If I scroll down to the Posting section, I will see my last bank account statement balance:

Dynamics 365 CSV Files

For more helpful tips & tricks for Microsoft Dynamics 365 Business Central, check out our blog posts or visit our YouTube channel.

Leave a Reply