Europe is preparing for SEPA, the Single Euro Payments Area. One of the SEPA goals is to replace all national direct debit standards by one XML based SEPA standard by 1 February 2014. This article shows how to create such an Xml file from Excel, without scripting.
Upgrading to Sepa
In the Netherlands – for example – the national file format for submitting direct debit orders (“incasso’s”) is currently ClieOp03, an ASCII flat file which is also used for credit transfers. Many commercial and non-commercial solutions are available to convert data from an Excel worksheet into this ClieOp format, most of which use some kind of scripting language like VBA.
One approach to upgrading a solution from ClieOp to SEPA Direct Debit (SDD) is to rewrite the script and replace the ClieOp output file with an XML-file which is compliant with the European XML standard PAIN.008.001.02 and in accordance with the ISO 20022 standard.
A scriptless solution
Another approach is to utilize Excel’s built-in XML-power (for a good introduction see: Overview of Xml in Excel by Microsoft): import the PAIN.008 schema into Excel, map the appropriate fields and validate/export the XML-file. Can we perform these steps just by using the Excel userinterface? The answer is: yes, we can! The attached xlsx-workbook proofs the concept. Download and open the workbook, go to the Developers tab and choose Export from the XML group. This will create an example message similar to Annex G in the SEPA Direct Debit Implementation Guidelines for the Netherlands.
To proof that the Xml-file is validated against the PAIN.008 schema clear one or more input fields and export the workbook data again. Excel returns a message-box as shown in the figure below:
For this to happen you have to set the property “Validate data against schema” in the XML Map Properties dialog to true (already done in the sample workbook).
Note that the detailed reason is in Dutch, even though the Excel display language was set to English (indeed, from Dutch). I suppose the Dutch message comes from a localized MSXML resource library.
By the way, the message says: “violation of minLength restriction of ‘1’. Element MsgId cannot be parsed.” Isn’t it great to get this kind of detailed feedback without any scripting?
Modifications to the schema
- A PAIN.008 message has three main levels: message, payment and transaction. A message contains one or more payments, and a payment – sometimes called batch – contains one or more transactions. In Excel it is not possible to implement a construct with two repeating levels, this would lead to a table within a table. We can solve this by allowing only one Payment group, as is done in the attached workbook. Now it is not possible anymore – for example – to submit one message with First and Recurrent transactions, which must reside in different payments. However, in practice this will not be a big issue: simply create two messages.
- Excel does not allow you to map elements that are part of a Choice construct. This can be solved by removing all choices from the schema. Fortunately, most of them are out of one choice, according to the Implementation Guidelines.
The attached workbook proofs that it is possible to utilize the Xml power of Excel to create and validate an Xml-file for Sepa Direct Debits, only using the Excel userinterface, without scripting. However, to implement this technique in a user-friendly Excel app needs a bit more effort. For example, to do an IBAN-check we need a script, since the schema only performs a pattern-check. So, there’s still some work to do.
Naturally, what we have done above for Sepa Direct Debit can also be done for the SEPA Credit Transfer, which is based on the PAIN.001.001.03 schema.
I hope this pixcel helps. Excel!