Deze webpagina geeft een introductie in het omzetten van een Excel- of een CSV bestand naar het Europese SEPA XML
betalingsbestand formaat.
Zowel het formaat voor betalingen (Credit Transfer) als de Europese incasso (Direct Debit) wordt door Excel2Sepa
ondersteund.
Daarnaast wordt in de vorm van een aparte module en gebruiksrecht de omzetting naar het
Nederlandse BTL91 bestandsformaat voor zakelijke buitenland betalingen (niet Euro) ondersteund door Excel2Sepa ten behoeve van ABNA AMRO rekeninghouders.
Inmiddels kunt met het Sepa formaat ook wereldbetalingen in andere valuta verrichten. Kijk dan eerst op de website van uw bank en vervolgens in onze
PDF quickstart manual hoe u dit met Excel2Sepa kan doen. Belangrijk hierbij is de te gebruiken adressen van de rekeninghouders, de waarde van de zgn.
Service Level Code en de te gebruiken valuta.
Allereerst maken we een Excel bestand aan, waarbij we bijvoorbeeld gebruik maken van het VoorbeeldSepa_CT_template.xls bestand bij zakelijke betalingen (Credit transfer) of VoorbeeldSepa_DD_Incasso_template.xls bij incasso's (Direct Debit).
Het eerste werkblad (Batch) van het template Sepa Excel bestand bevat de algemene Sepa gegevens, die voor het aanmaken van het
bestand nodig zijn. Voor nadere details wordt verwezen naar de beschrijving van de Sepa XML bestanden, zie de bijgeleverde PDF
gebruikershandleiding.
Het onderstaande schermvoorbeeld geeft u een indruk van deze gegevens. Een uitleg over de Sepa rubrieken wordt hieronder gegeven.
Wijzig overigens alleen de velden in kolom B (de velden met de witte kleur)
De kolommen C en D zijn alleen bedoeld als toelichting. Het programma Excel2Sepa doet er niets mee.
De feitelijke banktransacties worden in het 2de werkblad (Data) opgenomen, zie hieronder het schermvoorbeeld.
De eerste 16 kolommen (A t/m O) zijn vast en verplicht te gebruiken, anders werkt het Excel2Sepa programma niet correct. Als
alle gegevens zijn ingevoerd, wordt het Excel bestand opgeslagen.
Vervolgens wordt het Excel2Sepa programma opgestart, hetzij via het icoon op het bureaublad of door dubbel te klikken op het bestand Excel2Sepa.exe in de directory [disk]:\Excel2Sepa\bin.
Het programma opent een keuze window (Filemanager), waarin u het om te zetten Excel bestand kan kiezen.
Bij de keuze CANCEL of ANNULEREN stopt het programma de verdere verwerking. Heeft u een Excel bestand gekozen, dan start de verdere verwerking van het Excel bestand. Het programma Excel wordt geopend met het Excel bestand (ook wel werkboek genoemd), gecontroleerd wordt vervolgens of er 2 werkbladen zijn (Batch en Data). Zo niet, dan volgt een foutmelding en stopt het programma met de verdere verwerking, zodat u het Excel bestand nog eens kan controleren en aanpassen.
Indien u met de CSV bestanden wil werken, klik dan op het naar-beneden pijltje van de keuzelijst Bestandstypen, zodat de beschikbare CSV bestanden zichtbaar worden.
Tijdens het verwerken van het Excel wordt een window op
het scherm getoond, waar het aantal verwerkte rijen worden bijgehouden.
Zodra het Sepa XML/pain bestand is aangemaakt, wordt deze ook nog gecontroleerd of het een correct bestand is qua Sepa XML record layout met behulp van de PAIN.XSD validatie. Is de validatie geldig, dan kunt u het Sepa XML bestand importeren in uw internetbankieren programma of webapplicatie van uw bank. Krijgt u een melding dat de validatie een fout heeft opgeleverd, dan is dat meestal te wijten aan een tikfout of onjuist gebruik van een veldwaarde. Een korte uitleg van de XSD validatie vindt u hier.
Tijdens de verwerking kunnen door Excel2Sepa ook nog andere fouten worden geconstateerd, zoals het niet voldoen aan de Modulus97-proef van een IBAN bankrekeningnummer. Dit soort fouten worden bij het interactieve gebruik gemeld via een melding en in een log-bestand verzameld. Dit log-bestand staat dan in dezelfde directory als het invoer Excel of CSV bestand.
In plaats van een Excel bestand kunt u ook gebruik maken van een CSV bestand(en). Dit betekent dat de gegevens dan in het CSV
bestandsformaat zijn opgeslagen. De gegevens kunt u met een teksteditor invoeren of aanpassen. Daarnaast kunnen andere 'spreadsheet'
programma's de gegevens exporteren naar het CSV formaat.
Er zijn 2 manieren van aanmaken van het betalingsbestand. De eerste manier is om 2 CSV bestanden aan te maken. Het eerste CSV bestand bevat
de Batch gegevens, identiek aan het Excel werkblad 'Batch'. Het 2de CSV bestand bevat dan de banktransactie gegevens, identiek aan het Excel
werkblad 'Data'.
De tweede manier is om zowel de batch- als de datagegevens in één CSV bestand op te slaan.
In een CSV bestand worden de datavelden gescheiden door een delimiter zoals een komma (,) of een punt-komma (;) of een TAB.
Afhankelijk van de instellingen op je PC, kan bij het exporteren vanuit een spreadsheet programma de alfanumerieke datavelden tussen quotes (")
staan. Een CSV bestand heeft het filetype ".csv"
Meer informatie over CSV bestanden vind je op de Wikipedia link
Comma-separated_values - Specification".
Excel2Sepa checkt bij de eerste rij van het batch- en van het data CSV bestand, welke delimiter is gebruikt.
Ondersteunde delimiters zijn de [, ; = <TAB>] karakters.
De gebruikte Sepa rubrieken zijn in dezelfde volgorde, zie hiervoor de tabel met Sepa XML rubrieken. Er is wel een extra rubriek, te weten de plaats (directory) waar het CSV databestand is opgeslagen of ?intern? als u in het batch bestand vervolgd met de transactiedata.
Voorbeeld van het batch CSV bestand. Hieronder is een voorbeeld van een batch bestand weergegeven (met = als delimiter):
TypeUitvoer = Sepa
IBAN_Rekeningnr = NL34INGB0000294xxx
BIC_Code_bank = INGBNL2A
; voor ING=INGBNL2A ; Rabo bank=RABONL2U ; ABNAMRO=ABNANL2A
Naamopdrachtgever=Hr J W Teunisse
Adresregel_1=Balistraat 35
Adresregel_2=3818AJ Amersfoort
Adres_landcode=NL
SEPA_Incassant_ID=n.v.t.
SEPA_PmntInf_ID=zie naam_DD.CSV
Incasso_ZakelijkeBetaling=Z
Valuta=EUR
Aanmaakdatum=20200304
Betaaldatum=20200307
Omschrijving_1=Afscheid Bellevue Kas
Inzendersidentificatie=TEST
Bestandsidentificatie=0701
Batchvolgnummer=1
Data_bestand=D:\Excel2Sepa\VoorbeeldSepaData.csv
Commentaar regels: ter verduidelijking kunnen commentaar regels worden opgenomen, ter onderscheid van de gegevens beginnen deze regels links met of een punt-komma (;) of een hekje (#).
Gebruik in de CSV bestandsnaam het woord "batch", dan herkent Excel2Sepa het batch bestand bij het openen van de bestandsverkenner. Zie hieronder een voorbeeld.
De gebruikte velden zijn in de volgorde van links naar rechts, de eerste rij bevat de namen van de datavelden.:
Voorbeeld van het data CSV bestand (met ; als delimiter):
Naam Begunstigde;IBAN_Rekeningnr;BIC_Code_Bank;Valuta_soort;Bedrag;Adres_Begunstigde;Woonplaats_Begunstigde;LandCode;TransactieKosten;
Betalingskenmerk;Omschrijving 2;Machtigingskenmerk;DatumMachtiging;
J.W. Teunisse;NL2IBAN123456789;INGBNL2A;EUR;125,27;Straat huisnr;3818AJ Amersfoort;NL;shared;;voorbeeld 2de omschrijving;mref;2020-02-14;
Voorwaarden bij het gebruik van een CSV data bestand:
Rubriek | Waarde | Opmerkingen |
TypeUitvoer | Sepa of BTL91 | keuze voor het type Uitvoer bestand |
IBAN_Rekeningnr | Van welke rekeningnr wordt betaald/geïnd, het reknr opdrachtgever | |
BIC_Code_Bank | BIC-code van de bank van de opdrachtgever | |
Naamopdrachtgever | naam van de opdrachtgever volgens bank- of Interpaycontract | |
Adresregel_1 | vestigingsadres van de opdrachtgever - straatnaam | |
Adresregel_2 | idem - postcode woonplaats | |
Adres_LandCode | idem - ISO landcode, bijv. NL | |
Incasso_ZakelijkeBetaling | I of Z | betreft het een Incasso (Direct Debit) of Zakelijke terugbetaling (Credit Transfer) |
SEPA_Incassant_Naam | Bij incasso: (bedrijfs)naam op het incasso mandaat/contract met de bank | |
SEPA_Incassant_ID | Bij incasso: het identificatienummer op het mandaat: Creditor Identifier Het ID kent de volgende opbouw: NLnnZZZmmmmm0000, waarbij nn het controlegetal is; ZZZ het Creditor Business Code en mmmmm het KvK nummer, aangevuld met nullen |
|
SEPA_SequenceType | Bij incasso: geeft het type aan van de Direct Debit sequence zoals first, recurring, final or oneoff 1) | |
SEPA_CORE_B2B | Bij incasso: geeft aan of het bestand Zakelijke incassos (B2B) betreft of default van consumenten (CORE) 2) | |
SEPA_BatchBooking | true, false of 'leeg' | waarde: true of false (ING=true). Indien niet ingevuld ('leeg'), dan wordt de rubriek niet aangemaakt (ABN-AMRO = 'leeg') |
SEPA_PmtInf_ID | Wordt gebruikt in de SEPA tag <PmtInfId>, de waarde wordt getoond op het bankafschrift bij de transactie. Indien het veld leeg is, dan worden de eerste 30 karakters van de bestandsnaam gebruikt. 3) | |
Valuta | EUR | de gebruikte muntsoort |
Aanmaakdatum | 20130214 | Aanmaak datum van het bestand, formaat jjjjmmdd, NEN ISO norm |
Betaaldatum | 20130227 | Gewenste verwerkingsdatum, formaat jjjjmmdd, NEN ISO norm 4) |
Omschrijving_1 | Vaste Omschrijving voor alle data records in deze batch, wordt samengevoegd met de overige omschrijvingen 5) | |
Inzendersidentificatie | SEPA tag <MsgID>, 5 posities lang 6) | |
Batchvolgnummer | 1 | SEPA tag <MsgID>: kan gewoon 1 zijn, bij eenmalig gebruik per dag |
Noten:
De volgende letters, cijfers en tekens mag u, volgens de Sepa XML/pain vereisten, gebruiken in de veldwaarden
van uw Excel of CSV bestanden:
abcdefghijklmnopqrstuvwxyz (kleine letters)
ABCDEFGHIJKLMNOPQRSTUVWXYZ (hoofdletters)
1234567890 (cijfers)
/ - ? : ( ) . , ' + en de spatie
Deze karakterset behoort tot de Latin karakterset.
Let op: in de identificatie velden als Message-ID, PaymentInformation-ID, EndToEndId (uniek betaalkenmerk), Mandate-ID (uniek machtigingskenmerk) mogen geen spatie(s) worden gebruikt.
Gebruikt u per ongeluk andere tekens bijvoorbeeld de ampersand (&) in een (bedrijfs)naam, dan zal dit een fout opleveren bij het importeren
bij de bank.
Excel2Sepa vervangt deze tekens als & < > en ; door de XML escape tekens, echter in deze versie wordt een foutmelding gegeven
op de velden naam_opdrachtgever, omschrijving1, naam_betaler, adres_straat, omschrijving_2 en machtigingskenmerk. De foutieve
tekens worden vervangen door een spatie. Aan het eind van het verwerken stopt het programma, zodat u deze tekens door andere letters of woorden
kunt vervangen.
De definitie van de SEPA XML velden en hun onderlinge relatie zijn vastgelegd in zogenaamde XSD bestanden. Aan het eind van de Excel2Sepa verwerking wordt het aangemaakte Sepa XML bestand technisch getoetst aan de data definities, zoals deze in het overeenkomstige XSD bestand zijn vastgelegd. Het kan dus voorkomen, dat de validatie een fout vindt bijvoorbeeld in het IBAN nummer of zoals bij het getoonde voorbeeld in een foutieve BICcode.
Bij de validatie wordt gebruikt gemaakt van het Microsoft Windows ActiveX object "MSXML2.DOMDocument.6.0" (versie 6.0). Mocht op uw PC een oudere versie van dit ActiveX Object staan, dan krijgt u een foutmelding met de tekst 'Kan het Windows MSXml2.DOMDocument object niet aanroepen, waarschijnlijk door Windows niet de laatste versie op uw PC geïnstalleerd.' . In de configuratie file 'excel2sepa.ini' kunt u het gebruik van de validatie dan (tijdelijk) uitzetten. Zie hiervoor 'Veranderen instellingen Excel2Sepa'.
Indien u niet de laatste versies van dit MSXML2 object heeft, kunt u dat via de support website van Microsoft downloaden, bijvoorbeeld via een zoekmachine als Google. Zoek 'update windows msxml.dll' en volg de instructies van deze Microsoft support sites op, u dient versie 6.x van msxml.dll te installeren.
Op Windows 11 is versie 6.0 en op Windows 10 10 zijn standaard versie 3.0 en 6.0 geïnstalleerd.
In onderstaande foutmelding is een onjuiste BICcode gebruikt, de validatie valt hierover en geeft helaas een voor de
doorsnee gebruiker een onduidelijke systeem foutmelding.
In dit voorbeeld is te zien dat de toegepaste BICcode niet voldoet aan het formaat. Om het goed op te lossen, zijn de
onderste drie regels van belang. De eerste geeft tussen de <BIC> en </BIC> (XML-tag) de waarde van het
BICcode veld aan, nl. BICcode4. De 2de regel geeft aan dat deze waarde staat op regel 105 van de zojuist aangemaakte Sepa XML file.
Voor ons als gebruiker is het juist belangrijk om de BICcode in het bron bestand aan te passen, hiervoor gebruiken we de waarde van het veld
tussen de < en > XML-tag haken.
Hiernaast nog een voorbeeld van foutieve data invoer. Het betreft in dit geval een te lang betaalkenmerk.
Mocht je een dergelijk fout met de validatie krijgen, ga dan eerst proberen met de gevonden waarde dit in het bron bestand te
verhelpen.
Andere fouten kunnen zijn, als er bijvoorbeeld een fout in een XML-tag voorkomt, en de validatie deze XML-tag niet herkent.
Een dergelijke XML-tag fout graag z.s.m. per email melden aan info@jwteunisse.nl, zie hieronder bij het deel 'Problemen bij het gebruik', zodat we deze kunnen verhelpen.
Op dit moment zijn er slechts een 13-tal instellingen, waarmee u de werking van Excel2Sepa kunt beïnvloeden.
Deze hebben onder andere betrekking op:
De instellingen worden bewaard in een zgn. INI-file 'excel2sepa.ini'.
Dit tekstbestand staat in de [drive:]\Excel2sepa\Bin directory, waar de
excel2sepa.exe en ook de andere binaire files zijn opgeslagen. Hier
zijn ook de 3 pain*.xsd files (pain.001.001.02.xsd, pain.001.001.03.xsd en
pain.008.001.02.xsd) opgeslagen welke nodig zijn voor de validatie van het aangemaakte Sepa XML bestand.
De INI-file kunt u openen en aanpassen met een eenvoudige tekstbewerker als notepad (kladblok).<
Hieronder een deel van de INI-file:
; File exel2sepa.ini, ScriptVersie 3.2.3 Productie omgeving default d:\excel2sepa\bin ; by J.W. Teunisse ; Copyright 1999-2023 JWT Informatica- & Projectadvies te NL Amersfoort (email info@jwteunisse.nl) ; bevat zowel de settings voor de test- als de productie-configuratie [Main] ; Licentie maakt keuze voor SEPA (default) Licentie=SEPA ; Valideren van SEPA XML file: JA = uitvoeren, NEE = overslaan SEPA_Validatie = JA ; Excel op scherm tonen: JA of NEE Excel_Visible = JA ; Bereken SHA1 en de SHA256 controle getal over de Sepa XML file en toon in de log-file <bestandsnaam>.log Bereken_SHA1=NEE ; laatst gebruikte directory voor de bronbestanden last_used_dir=D:\Excel2Sepa_JWT\ [SEPASettings] ; Sepa credit transfer welke pain versie (3 of 9), na 17-03-2024 keuze dient 9 te zijn SEPA_CT_painversie=3 ; Sepa direct debit transfer welke pain versie (1 of 8), na 17-03-2024 keuze dient 8 te zijn SEPA_DD_painversie=1 ; wel of niet XML commentaar toestaan in de SEPA XML file: JA of NEE SEPA_XML_Comment = NEE ; samenvoegen van betaalkenmerk en omschrijving (recommendatie banken) aan of uitzetten: JA is default SEPA_Kenmerk_in_Omschr=JA ; opnemen Betaalkenmerk in <EndToEndId> tag: JA is default SEPA_Kenmerk_in_EndtoEnd=JA ; gebruik het woord PAY-ID in de <EndToEndId> tag, als het kenmerk wordt gebruikt: JA is default SEPA_use_payid=JA ; Voor Deutsche Bank onderstaande optie(s) op JA zetten, default is NEE (Uit) SEPA_CT_DebtorAccount_CCY=JA SEPA_DD_CdtrAccount_CCY = JA ; optie voor opnemen van de incasso Mandant Naam <Nm> tag bij de tag <CdtrSchmeId>: RABObank is NEE SEPA_DD_CdtrSchemeId_Nm=JA Print_Summary=NEE [SEPABatch] ;eventueel uitvoeren van een extra controle op Sepa incasso contract voorwaarden. Default wordt er geen controle uitgevoerd bij de Direct Debit incasso's: waarde NEE. SEPA_max_controle=NEE SEPA_max_bedrag=500 SEPA_max_batchbedrag=40000 SEPA_max_posten=100 [Logging] ; nog niet in gebruik [End] ; modifications: ; aangepast voor nieuwe versie excel2sepa/sepatica ; ; eof file
Bij het gebruik van het Excel2Sepa programma kan het zijn dat je niet direct een correcte SEPA XML (of BTL91) bestand krijgt aangemaakt. Vaak is dit het gevolg van tikfouten in het invoerbestand of onjuist gebruik van de Sepa (of BTL91) waarden. Lees dan vooral eerst de bijgeleverde Sepa (of BTL91) documentatie. Mocht je er dan toch nog niet uitkomen, stuur dan een email naar info@jwteunisse.nl met een duidelijke probleemomschrijving, de gebruikte Windows en Excel versie en voeg waar mogelijk een schermafdruk toe.
topDe laatste versie van Excel2Sepa is versie 3.2.3, build 3-06-2020.
De voorlaatste versie van Excel2Sepa is versie 3.2.2, build 29-11-2017.
Sepa:
In deze onderhoudsversie: herstel van enkele fouten zoals het berekenen van de SHA-256, waarbij de Excel filenaam spatie(s) bevatte.
Een fout in de licentie controle, waarbij in een enkel geval de gebruiker een melding kreeg dat de licentie was verlopen..
De versie 3.2.1 van Excel2Sepa, build 21-11-2016.
Sepa:
De versie 3.1.1N van Excel2Sepa met build 4-01-2016:
Deze en oudere versies worden door ons niet meer ondersteund sinds de komst van Windows 10.