Reviews and corrects ledger-to-subledger alignment in D365 by fixing posting configurations, inventory profiles, reconciliation logic, GL mapping, and critical reporting procedures.
How to Define a CSV File for Bank Statement Imports in Business Central
Posted on: October 19, 2020 | By: Guy Logan | Microsoft Dynamics Business Central
Microsoft Dynamics 365 Business Central, an eminent ERP and CRM solution, allows businesses to easily reconcile their bank accounts by importing bank transactions. In this blog post, we will explain how to define a CSV file for a bank statement imports in Business Central.
In order to match transactions, a bank statement import format needs to be set up to enable mapping of the bank reconciliation. Before beginning the reconciliation process, it is important to download the bank statement file from the bank and gain an understanding of what each column represents, as this will be necessary in order to map the bank file to the bank reconciliation fields.
Step-by-Step Process
1. Search for the window ‘Data Exchange Definitions.’
2. Click New and enter in a Code and Name. Fill in the fields with the following information:
3. In the Line Definitions section, enter the number of columns that will be in the CSV file in Column Count.
4. Scroll down to the Column Definitions section and fill in the columns from the CSV file. Number the columns 1, 2, 3, 4, etc. and enter a name for each column. The column ‘No’ will need to match the column number in the CSV file.
- For dates, Choose Date as the Data Type and enter the format from the file. If the date shows 10/15/2020 in the file, then enter “m/d/yyyy.” In the Data Formatting Culture field, enter “en-us.”
- For amounts, enter Decimal as the data type and “en-us” in the Data Formatting Culture.
5. Scroll back up to the Line Definitions section and select Manage > Field Mapping.
6. Click New to map the fields from the CSV file to the fields in BC, and fill in the following fields highlighted below.
7. In the Field Mapping section, select the first column on the first line. Then, select the corresponding column in Field ID.
8. Repeat until all columns have been mapped. (Note: Only the Transaction Date and Statement Amount fields are required.)
9. Search for “Bank Import/Export Setup” and open the corresponding window. Select New, enter a Code and Name, and fill in the highlighted lines below:
10. Scroll to Data Exch. Def. Code and select the Data Exchange Definition that was generated in the previous steps:
11. Finally, navigate to the bank account, scroll to Transfer in the Bank Statement Format field, and assign the bank account that was just created.
Next steps
Logan Consulting has helped various brands transform their business using Microsoft Dynamics 365 Business Central and other similar services. If you are interested in Business Central or want to find out how Logan Consulting can help you optimize your business, contact us today for a consultation.
