Import a .CSV file for Bank Reconciliation in Business Central

Introduction:
This blog will guide you in importing a .CSV file for Bank Reconciliation.

Pre-requisites:

  • Dynamics 365 Business Central

Steps:
Step 1:  Search for Data Exchange Definition in  Tell me/Search Icon and open it, Create a new Data Exchange Definition and enter the code and name.



Step 2: In the new Data Exchange Definition add the following fields.
  1. File Type - Variable Text
  2. Type - Bank Statement Import
  3. Reading/Writing Xml Port - 1220
  4. Ext Data Handling Codeunit - 1240
  5. You can also add the number of Header lines that are present in .CSV file in the Header Lines field. I have one Header Line so I have put 1.


Step 3: In the Line Definitions Tab, enter the Code and Number of Columns that are present in the .CSV file. I have 7 columns so I've put them accordingly.



Step 4: In Column Definitions put Name of Columns according to your .CSV file and their Data type as well.



Step 5: Now click the Field Mapping Action on Line Definitions tab.




 Step 6:  The Field Mapping window opens, Create a new one and populate the following fields:

  • Write the Name of the Mapping.
  • Table ID - 274
  • Mapping Codeunit - 1248

In .CSV file we have Two columns Debit and Credit, and we have mapped both of them in the Statement Amount field of the "Bank Acc. Reconciliation Line" table, so to differentiate between these two we put Credit Amount as Negative, How do we achieve it? I have a solution for this, I tried using Transformation Rule but it didn't really work for me, so I added one hidden field "Multiplier" using Personalize in Field Mapping Tab which shows Multiplier as "1", in this field I have changed it to "-1" for "Credit" field.



Step 7: Search for "Bank Import/Export Setup" and open it. Create a new Bank Import Export Setup.

  • Add Code and Name of New Bank Import/Export Setup
  • Processing Codeunit ID - 1270
  • Data Exc. Def. Code -  The Data Exchange definition we have created ie. TESTIMPORT

Step 8: Now go to the Bank Account for which you have your statements to reconcile in .CSV file and add your above created Bank Import Export Setup in the "Bank Statement Import Format" field.




 Step 9: Go to the Bank Account Reconciliation page and create a new one and select the Bank account for which you want reconciliation to be done.
Click Action "Import Bank Statement" on the Bank Account Reconciliation Card page.



Select the .CSV file and viola!! You have your .CSV file successfully imported in Business Central for reconciliation.

Conclusion: 
This is how we can achieve importing a .CSV file in Business Central for Bank Reconciliation.


Hope this helps!

Comments

  1. Unknown7 September 2020 at 23:42

    Very helpful

    ReplyDelete
  2. Shahid10 January 2024 at 23:06

    Thanks for nice post on Bank Reconciliation. But in my case, it is only taking one value which is deposit amount but i have mapped both deposit account and Withdrawal account to Statement Amount, could you please give guide what could be the reason

    ReplyDelete

Post a Comment

Popular posts from this blog

Update Sub-form (Sub-Page) from Main Page to apply Filters.

Image
Problem Statement : I have a requirement where I want to update the Subform(Part page) from the Main page, this Subpage(Subform) is linked to the Main page by SubpageLink Property. The Subpage is a List page and I want to filter this list by a field from Main Page. Pre-requisites: VS Code AL Language Extension Microsoft Dynamics NAV /  Business Central Solution: To solve the above Problem statement we have to make use of the Currpage.Update() function which shall update the Subpage.  As you can see in the below window a function is created Vendorfilter() on Subpage with the "vendor" parameter, in this function filter is added for the vendor on the part page and then CurrPage.Update() is used. This function will update the Subpage with the vendor.    Now, call this function on "OnValidate" trigger of the Main page field, here we have "Vendor" field on Main Page.     The calling of Function is as follows: CurrPage.<Subpage...
Read more

How to retrieve a Sub-string from a String

Image
Problem Statement : We have a requirement where the client wants a part of String from Sales Order No. which is Code(Data type) and it is auto-generated from Number Series. The Sales Order No. value is "SO/123/789/456". The required Sub-string was: 123<Space>789. Pre-requisites: VS Code AL Language Extension Microsoft Dynamics NAV /  Business Central Solution: As per the requirement, we need to first retrieve Sub-strings "123" and "789" and this can be achieved using SELECTSTR( ) function. But SELECTSTR() only retrieves a Sub-string from a comma-separated String. And the Sales Order No. string is a slash-separated string. So firstly we need to convert this string to comma-separated string, which is achieved in the below-presented screen. CONVERTSTR(String, From Characters, To Characters)   In the above Screen, "SalesOrder No." is initialized in a text variable "SoNumber" and then is converted to a comma-sep...
Read more