Many organizations import banktransactions into Microsoft Dynamics 365 Business Central SaaS via CSV files. This works quickly and efficiently, especially when a bank does not provide standard CAMT.053 files or when you import transactions from an external application. However, a problem often arises when reconciling bank transactions: the transaction description is not always clear enough to automatically find the correct entry.
In this blog, I will show how you can solve this problem with a relatively simple AL extension. We will first look at how CSV imports work within Business Central, what problems can arise with automatic reconciliation, and how you can enrich transaction texts using an event subscriber so that Business Central is much better able to automatically recognize transactions.
The problem with the import of banktransactions via CSV
When you import a bank statement via a CSV file, each transaction line usually contains a limited number of fields, such as:
- Date
- Amount
- Transaction description
- Related-party name
- Reference
In practice, the Transaction Text field in particular often provides insufficient information.
For example, consider a payment at the Action retail chain in Veldhoven:
Action VeldhovenHowever, the CSV file contains:
1447 VeldhovenA person might still be able to recognize this, but for Business Central it is difficult. The system does not know that “1447 Veldhoven” actually refers to a payment to Action.
The result is that automatic reconciliation works less effectively.
Why automatic reconciliation depends on recognizable texts
The payment reconciliation journal compares imported bank transactions with outstanding entries in the administration.
Business Central uses various characteristics for this:
- Amount
- Date
- Relationship
- Payment reference
- Description
The description plays a particularly important role in:
- supplier payments
- recurring expenses
- retail transactions
- private administrations
- association administrations
When a supplier is known in the administration as “Action Nederland B.V.” but the bank transaction only contains “1447 Veldhoven”, Business Central is missing an important recognition point.
As a result, the automatic recognition rate decreases significantly.
The advantage of enriched transaction texts when you import banktransactions via CSV
Suppose we automatically modify the description during import.
1447 VeldhovenTo:
Action - 1447 VeldhovenThis creates a much clearer description that better aligns with payment rules and reconciliation logic.
The result:
- higher automatic recognition
- less manual application
- faster processing of bank transactions
- fewer errors
Working with an Event Subscriber when you import banktransactions via CSV
Business Central provides various events that developers can use to extend standard functionality without modifying source code.
In this example, the following is used:
OnAfterInsertEventThis means that the code is executed automatically as soon as a new line is created in the payment reconciliation journal.
This allows us to modify the transaction text immediately after importing, before the user starts reconciling.
Structure of the solution
There are two important fields within the extension:
RelatedPartyName := Rec."Related-Party Name";
TransactionText := Rec."Transaction Text";The name of the counterparty is the first variable and the second contains the imported description. The last line of code then checks whether known patterns occur in the transaction text.
Example: Action
The following check searches for the text:
1447 VeldhovenWhen this is found, the code replaces the text with:
Action - 1447 VeldhovenThis immediately makes the transaction clearer.
Why use a custom ReplaceText function when importing bank transactions via CSV?
The extension uses a separate function:
local procedure ReplaceText(...)This function searches for a text fragment and replaces only that part.
This has several advantages:
- clear code structure
- easy to extend
- reusable for other recognition rules
When additional stores are added later, the same function can be used again.
For example, consider:
- Jumbo
- Albert Heijn
- Lidl
- Kruidvat
- Decathlon
- Gamma
This gradually creates an increasingly intelligent recognition mechanism.
Using the related-party name when importing bank transactions via CSV
Not every transaction requires a specific recognition rule.
That is why the extension also includes a generic solution.
When the related party is available:
RelatedPartyNameit is automatically added to the beginning of the transaction text.
For example:
Original
VELDHOVEN, 5508ME, NLD, 12:11After processing
Nuvoc kantine VELDHOVEN, 5508ME, NLD, 12:11This provides much more context during reconciliation.
Preventing duplicate texts
An important part of the solution is the check for duplicate additions.
The code first checks:
if StrPos(TransactionText, RelatedPartyName) > 0 then
exit;This prevents the extension from adding the same name multiple times.
Without this check, descriptions could be generated such as:
Belastingdienst Belastingdienst Teruggaaf MotorrijtuigenbelastingThat would actually cause more confusion.
When is the change saved?
As soon as a change is required, the extension writes the modified text back to the record.
This is done with:
Rec."Transaction Text" := CopyStr(...);The change is then saved:
Rec.Modify(false);This updates the data immediately without any additional user action.
The user notices virtually nothing, except that transactions receive clearer descriptions.
Alternatives to an AL extension
There are several ways to solve this problem:
1. Payment Matching Rules
Business Central supports standard payment reconciliation rules.
These allow you to search for keywords.
The disadvantage is that the text often needs to be sufficiently recognizable for this. With descriptions such as “1447 Veldhoven”, this method falls short.
2. Modifying the CSV file
You can enrich the descriptions before the file is imported.
However, this requires additional processing outside Business Central.
3. AL extension
The described solution has several advantages:
- fully integrated
- automatic
- maintainable
- scalable
- no manual intermediate steps
For many organizations, this is therefore the most future-proof approach.
Opportunities for extension when you import banktransactions via CSV
The described extension provides a good foundation but can be extended further.
For example, consider:
- recognition of supermarket chains
- petrol stations
- online stores
- insurers
- energy suppliers
- telecom providers
A separate configuration table can also be developed in which users manage recognition rules themselves without having to publish new software.
This would create, for example, a table with:
| Search text | Replace with |
|---|---|
| 1447 Veldhoven | Action – 1447 Veldhoven |
| BCK*Warenhuis Witte Ve | Hema |
| AH To Go | Albert Heijn To Go |
| Jumbo Valkenswrd Waa | Jumbo Valkenswaard |
This makes the solution even more flexible.
Conclusion: importing banktransactions via CSV
Importing bank statements via CSV files into Business Central SaaS is therefore a practical solution when standard bank connections are not used. However, the quality of the reconciliation process depends heavily on the information available in the transaction texts.
When banks or payment providers supply cryptic descriptions, the effectiveness of automatic reconciliation decreases significantly. With a relatively simple AL extension on the Bank Acc. Reconciliation Line table, you can automatically enrich these descriptions. Specific recognition rules for stores such as Action and HEMA, combined with adding the related-party name, make transactions much easier to recognize.
The result is therefore a higher percentage of automatically reconciled transactions, less manual work, and a more efficient reconciliation process. For organizations that regularly process CSV bank files in Business Central SaaS, this approach can make a surprisingly large difference in day-to-day administration.
Would you like to learn more about this topic? Then take a look here. Would you like to contact me? Then click here.
