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.
Final notes
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!
Hi Frans,
I used Your excel file but the extention is missing for my file may be you can advise me ?
I could send my file with my comments and could you give me some assistance.
When I tried to import it into the bank I got the faillure Presenters ID tag is missing.
What need to be changed so I can use it correctly
With kind regards,
Hi Frank,
I am already using your Excel->Sepa for a couple of years. Thanks for getting this simply working. My workflow is based on a monthly update of the Excel file, remove the ns1 stuff with a one click (a simple autohotkey script) and send it to my bank (ABNAMRO). The complete monthly workflow cost me less than 10 minutes.
However now ABNAMRO is changing/simplifying the process. FRST is not used anymore, only a SMNDA should be put in field 2.98 instead of 2.99 when a debtor changes to a new IBAN. Any idea if this needs a Excel XML-mapping change? And if yes how to get this done?
Thanks !
Jan-Pieter
Jan-Pieter,
Do you have the excel pain.001.001.03 i am facing problems and i have no experience! I need your help!!
Thanks in advance
Hi Frans,
How can I get the tool to generate the SEPA xmls from a Excel that you developed? I see here that people is using it but I don’t understand where to download it from.
Could you please help?
Thanks a lot
Michel
Hello sir,
Thank you for sharing your soulution, I found it really great.
If it’s not too much, can you please make example or two what you exactly changed in part “Modifications to the schema” for step 1 and step 2. What lines in XSD file you modified?
Thank you.
Hello Zovche,
You can reproduce the schema by saving the string Thisworkbook.XmlMaps(1).Schemas(1).XML (in the above enclosed workbook) to a text file. In this schema all lines that are modified are commented out.
Regards,
– Frans
Hello Frans,
thank you for the tip. I think I get it working now for Credit Transfer as well.
Just one more question (in Credit Trasfer there are optional fields in one case but mandatory in another one), so in this example if we leave some field mapped but blank, that element will show in XML anyways with blank result. Do you know if it’s possible to define if some mapped cell is blank that it’s not showing in exported XML?
Kind regards,
Zovche
I am also very interesed in getting this file for SEPA Credit transfer!
I appreciate your efforts am hope that we see soon a result.
Is there any chance to get the file for SPEA Credit transfer?
Hi Jan,
Still working on it. Too busy, I’m sorry.
– Frans
Hi,
I’m trying to do the same for the PAIN.001.001.03 schema.
But when I drag the element /Document/CstmrDrctDbtInitn/PmtInf/CdtTrfTxInf/PmtId/EndToEndId I always get the message that the document is not exportable because it contains “List of Lists”
Can you help me?
Thank you.
Hi Rui,
To prevent this you have to modify the schema. See “Modifications to the schema” above, point 1.
Regards,
– Frans
Hi Frans,
How can i changed it not to take a list but only one value?? From where should i make the change??
Hi there, fantastic idea. Does anybody have the same version for SEPA Credit transfer? It would save me a lot of time trying to develop it. Cheers and keep up the good work guys,
We hebben de tool ingezet voor onze stichting United Cubs. Geweldig, works like a charm. Dank je wel!
Thom
perfect man, this is what I was looking for.
When you search the web, it starts with all kind of paid solutions for generating PAIN xml.
As I only have to collect form 20 or what people, every euro spent counts.
I allready thought that the XML generator of excel should do the trick. Good to see, that it does.
thanks,
jan
Hoi frans, ben je nog bezig aan een “simple excel app” zoals je hierboven zei??
Alvast bedankt
Gijs
????? no ideas so?????
any ideas???
9 and 16 are the rows where the failure notice was and actually read as below . . . didnt come out the first time for some reason
9
16true
Someone also mentioned to me that our Bank (Bank of Ireland) does not support the EU Standard…. whatever that means!!
here is a link to file spec required by BOI
https://businessbanking.bankofireland.com/fs/doc/wysiwyg/sepa-direct-debit-pain-008-001-02-xml-file-structure-july-2013.pdf
thanks Fran, really hope you can help me out
Also
16: true
NOTICE Bank specific rules may apply to processing of batch booking.
Read more from: Wiki link https://wiki.xmldation.com/Support/EPC/BtchBook
Notice origin: level #2 (IPSO DD Business Rules)
XPath to the element: //ns1:Document[1]/ns1:CstmrDrctDbtInitn[1]/ns1:PmtInf[1]/ns1:BtchBookg[1]
This is the failure report I received when I tried to validate the file
9:
ERROR Mandatory element ‘InitgPty/Id’ missing.
Error origin: level #2 (IPSO DD Business Rules)
XPath to the element: //ns1:Document[1]/ns1:CstmrDrctDbtInitn[1]/ns1:GrpHdr[1]/ns1:InitgPty[1]
This looks great… till I tried and failed 🙁
Any one got any idea what I might be doing wrong?
Hi Stephen, please explain what failed?
Regards,
– Frans
Perfect solution, saved our foundation a lot of money!
On a sidenote, the resulting XML file is imported into Rabobank Internet banking without any problems (so unlike ING there is no need to get rid of the ns1 mentions).
Frans
Importeren bij ING is ook gelukt maar pas nadat ik het xml bestand ontdaan had van :ns1 en ns1:
Mischien is daar nog iets voor?
Groet Jan
Jan, mooi dat het gelukt is. Door een andere keuze in de (default) namespaces in het schema is daar misschien wel iets aan te doen. Zoniet dan kan het AfterXmlImport event wellicht uitkomst bieden. Ik kom er via de mail nog wel op terug.
Groet,
– Frans
Dag Frans,
ik heb hetzelfde probleem; zonder de :ns1 en ns1: werkt het goed maar die moet ik handmatig verwijderen; ik ben absoluut geen xml expert dus ik begrijp niet wat ik zou kunnen/moeten veranderen om die namespaces er niet in te krijgen; fijn als je daar een oplossing voor hebt!
Dankjewel
Michèle
Hoi Frans!
We zijn inmiddels een paar jaar later, maar ik kom zojuist jouw excel tegen. Heb je inmiddels een verbeterde versie gemaakt die alle bovenstaande bugs niet meer heeft?
Gr
Dennis
Heb ik nu gedaan en met een aantal aanpassingen heb ik nu een groen vinkje thxs.
nu nog in de echte wereld. ik denk dat ik mij iets meer ga verdiepen in xml
Beste Frans,
hoe kan ik de 1e twee export regels veranderen van
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><ns1:Document xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">
In
<?xml version="1.0" encoding="UTF-8"?><Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
Want dit is de layout die mijn ing bank wil hebben. Volgens mij lukt het voor de rest aardig. Groet Jan weber
Hi Jan,
Nice to hear this is working for you.
I don’t think it is necessary to change the two output lines. Have you tested your export with ING’s Format Validation Tool?
Please keep in mind: the download above is a proof of concept, not a full-proof application.
Regards,
– Frans
Hi. I hope you can read this. I am in the same situation as Jan Weber: I need to remove that “ns1″ tag or whatever it’s called, and also need that xmlns:xsi=”https://www.w3.org/2001/XMLSchema-instance”
Is it possible to edit that on your otherwise wonderful spreadsheet? If so, how?
Thank you so much indeed.
Hoe kan het beste van een excell bestand een SEPA bestand maken? Ben een kleine vereniging. En lever momenteel een batch aan in clientop3. via Internet Banieren ABN.
ik ben al uren aan het zoeken en heb tot heden weinig gevonden. Wel een paar pakketten die ik kan kopen. Is er ook een oplossing die niet gelijk honderden euros kost? Weet iemand een oplossing?
Hello feliss,
I am working on a simple excel app. Stay in touch!
Regards,
– Frans
beste Frans, ja fraaie oplossing. echter in de file die ik er mee maak komt steeds …ns1:….. voor.
PIXSDD20130501
doe ik iets niet goed of heb ik een onjuiste versie van Excel….
groeten Frank
Hallo Frank,
Nee je doet niets fout: ns1 is een verwijzing naar de namespace die bovenin de xml wordt gespecificeerd.
Groet,
– Frans
bedankt, de oplossing die jij hebt gemaakt is voor t innen van gelden bij debiteuren, niet? ik zoek iets voor t betalen van bedragen aan crediteuren (nu nog via CLIEOP03). is dat ook op een zelfde manier te realiseren? groet Frank
Ja dat klopt. En inderdaad, het kan op vergelijkbare wijze voor SEPA Credit Transfer. Zie de laatste zin van het artikel. Ik ben bezig een app te ontwikkelen die beide kan.
Groet,
– Frans
dat is geweldig. we wachten er met smacht op :).
succes.
groet Frank
Outstanding information…
I also in providing services on SEPA Direct Debit and SEPA Credit transfer in UK countries would like to share the information on hoe to get the total SEPA report in simple XML file without scripting.