• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / XML stuff / Sepa Direct Debits: from Excel to Pain

Sepa Direct Debits: from Excel to Pain

2013/06/18 by Frans

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:

XML Parse Error
XML Parse Error

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

  1. 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.
  2. 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!

Download

SepaDirectDebit-FromExcelToPain-v1.xlsx

Filed Under: XML stuff Tagged With: Direct Debit, PAIN.008, SEPA, xml

Reader Interactions

Comments

  1. Jolanda van Beelen says

    2017/04/25 at 13:53

    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,

  2. Jan-Pieter says

    2016/09/27 at 22:57

    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

    • Chara says

      2016/10/14 at 14:22

      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

  3. Michel says

    2016/05/04 at 11:01

    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

  4. Zovche says

    2016/04/27 at 10:12

    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.

    • Frans says

      2016/04/28 at 19:51

      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

      • Zovche says

        2016/05/23 at 13:21

        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

  5. ME says

    2016/02/10 at 12:37

    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.

  6. Jan Jonker says

    2016/01/25 at 15:53

    Is there any chance to get the file for SPEA Credit transfer?

    • Frans says

      2016/01/31 at 22:52

      Hi Jan,
      Still working on it. Too busy, I’m sorry.
      – Frans

  7. Rui Ferreira says

    2016/01/20 at 19:12

    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.

    • Frans says

      2016/01/31 at 22:59

      Hi Rui,
      To prevent this you have to modify the schema. See “Modifications to the schema” above, point 1.
      Regards,
      – Frans

      • Chara says

        2016/10/14 at 13:00

        Hi Frans,
        How can i changed it not to take a list but only one value?? From where should i make the change??

  8. Pablo says

    2015/07/09 at 17:10

    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,

  9. Thom Rommens says

    2015/03/24 at 17:20

    We hebben de tool ingezet voor onze stichting United Cubs. Geweldig, works like a charm. Dank je wel!
    Thom

  10. jan says

    2014/12/24 at 18:29

    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

  11. Gijs Manders says

    2014/12/04 at 01:27

    Hoi frans, ben je nog bezig aan een “simple excel app” zoals je hierboven zei??

    Alvast bedankt

    Gijs

  12. Stephen O'Brien says

    2014/09/16 at 11:01

    ????? no ideas so?????

  13. Stephen O'Brien says

    2014/08/28 at 11:18

    any ideas???

  14. Stephen O'Brien says

    2014/08/20 at 17:09

    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

  15. Stephen O'Brien says

    2014/08/20 at 17:04

    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]

  16. Stephen O'Brien says

    2014/08/20 at 17:01

    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]

  17. Stephen O'Brien says

    2014/08/19 at 18:33

    This looks great… till I tried and failed 🙁
    Any one got any idea what I might be doing wrong?

    • Frans says

      2014/08/19 at 23:22

      Hi Stephen, please explain what failed?
      Regards,
      – Frans

  18. Joost says

    2013/09/09 at 21:09

    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).

  19. Jan Weber says

    2013/09/02 at 16:37

    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

    • Frans says

      2013/09/02 at 20:41

      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

      • Michèle says

        2015/11/02 at 22:48

        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

      • Dennis Hoogeboom says

        2018/10/23 at 22:11

        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

  20. Jan Weber says

    2013/09/02 at 13:50

    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

  21. Jan Weber says

    2013/08/30 at 16:06

    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

    • Frans says

      2013/08/30 at 22:18

      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

      • Roy Smith says

        2017/10/31 at 17:59

        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.

  22. feliss says

    2013/08/26 at 00:43

    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?

    • Frans says

      2013/08/26 at 12:29

      Hello feliss,
      I am working on a simple excel app. Stay in touch!
      Regards,
      – Frans

  23. Frank de Koning says

    2013/08/22 at 19:59

    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

    • Frans says

      2013/08/26 at 12:28

      Hallo Frank,
      Nee je doet niets fout: ns1 is een verwijzing naar de namespace die bovenin de xml wordt gespecificeerd.
      Groet,
      – Frans

      • Frank de Koning says

        2013/08/28 at 16:36

        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

      • Frans says

        2013/08/30 at 22:21

        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

      • Frank de Koning says

        2013/09/02 at 23:17

        dat is geweldig. we wachten er met smacht op :).
        succes.
        groet Frank

  24. SEPA Direct Debit says

    2013/07/17 at 14:30

    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.

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in