Transform a camt053 bank statement to Excel

Anonymous · January 3, 2023, 9:56am

Use can use this free xml parser that will make viewing and editing xml file easy, easier than Sublime Text.

Here is the file I found on net, but I don’t know if it is correct and honestly I don’t understand the data that contains in it, if this is how your file looks like, then it would be nice, if you could explain which field you are interested in it and what do they stand for. Or you could make changes in this sample file with dummy data and tell us your desired output.

Sample file from the net.
camt_053_swedish_account_statement.xml (7.9 KB)

Admin · January 5, 2023, 11:26am

Thanks for finding that sample file, I didn’t find one when I looked.

There are some guides online on what each of the headers means.

I notice there are a lot of empty and single value columns. I have some ideas to make it easier to discard those.

Admin · January 5, 2023, 11:27am

Where did you get the same file from? Are there any more samples?

CristianoR · January 7, 2023, 11:31am

Today I found this page with detailed information about the banking payment standards for Switzerland. It has some downloadable documents with information about the ISO2022 standard that is being implemented by the Swiss banks.

Anonymous · January 9, 2023, 8:26am

Hi,

From this link

Go to the Example Files section.

ChristerH · April 8, 2024, 12:22pm

How is this going? No activity for a while, but seems like it could match some of my oproblems.
I tried to make some filters and renaming columns and output to a simpler layout with the files we get from the bank.
My trouble is that if I set up so it works, next file doesn’t have all columns as the one before, so my filters uses the wrong columns when I try again.
The filter doesn’t seem to connect to the columns I setup, it seems to the be the the order the columns is found.
Is it better to use CAMT53 or CAMT54?
My goal is to convert to a CSV that is accepted in our old system.

frameexpert · April 8, 2024, 1:10pm

One suggestion: Sometimes it is better to use another tool to “normalize” the XML. I use XSLT (a W3C standard language for converting XML to XML and other formats) to isolate the elements and attributes that I need. Then I use EDT to isolate/modify the data because now it is in a consistent format.

I downloaded the sample camt053 XML to play with. Can you post a sample of the CSV output that you are looking for? Thank you.

Admin · April 8, 2024, 3:47pm

You handle changes in column ordering and missing columns using the Stack transform. Please see:

ChristerH · April 9, 2024, 5:42am

This is an example of what I want to be in my output.
I sort out in the xml all payments done by customers.

Date;Reference;Name;Amount;Currency
2024-04-02;93870 33442480;COMPANY A;1533.00;SEK
2024-04-02;93890;COMPANY B 1;3870.00;SEK
2024-04-02;INVOICE 93877;COMPANY C;2026.00;SEK
2024-04-02;INVOICE 93869;COMPANY C;938.00;SEK
2024-04-02;93880;COMPANY D 30;2596.00;SEK
2024-04-02;93880;COMPANY D;11468.00;SEK

ChristerH · April 9, 2024, 6:38am

Stack seems to make life easier.
WIll try with some more files, thanks!

Admin · April 9, 2024, 7:22am

We have some ideas about how to better handle ‘schema drift’ (e.g column ordering changes) in v2.

frameexpert · April 9, 2024, 12:28pm

OK, that is your output. Can you tell us which elements in the XML go in each column of the CSV?

Date
Reference
Name
Amount
Currency

ChristerH · April 9, 2024, 12:51pm

I have some magic done before it becomes a csv.

These columns is my output (Swedish names).

Concat makes the column in the picture before, I need to put two columns together since our invoice number is in different fields depending on sending customer.

concat

And I also discovered that the camt54 is better/easier for me to use.

frameexpert · April 9, 2024, 1:11pm

Can you post a camt54 sample? Thanks.

frameexpert · April 9, 2024, 2:02pm

I found a sample, but I am not sure if it is the same as yours. Anyway, here is an example of using XSLT to “normalize” the XML. This stylesheet:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:math="http://www.w3.org/2005/xpath-functions/math"
    exclude-result-prefixes="xs math"
    version="3.0" expand-text="yes" 
    xpath-default-namespace="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04">
    
    <xsl:output indent="yes"/>
    
    <xsl:template match="/">
        <data><xsl:apply-templates select="//Ntry"/></data>
    </xsl:template>
    
    <xsl:template match="Ntry">
        <entry>
            <Datum><xsl:value-of select="BookgDt/DtTm => replace('T.+$','')"/></Datum>
            <Namn><xsl:value-of select="NtryDtls/TxDtls/RltdPties/Dbtr/Nm"/></Namn>
            <Belopp><xsl:value-of select="NtryDtls/TxDtls/Amt"/></Belopp>
            <Valutakod><xsl:value-of select="NtryDtls/TxDtls/Amt/@Ccy"/></Valutakod>
            <Referens></Referens>
        </entry>
    </xsl:template>
</xsl:stylesheet>

produces this output:

<?xml version="1.0" encoding="UTF-8"?>
<data>
   <entry>
      <Datum>2010-10-18</Datum>
      <Namn>MUELLER</Namn>
      <Belopp>0</Belopp>
      <Valutakod>SEK</Valutakod>
      <Referens/>
   </entry>
</data>

You can use more general XPath addresses to account for differences in the input files. Then you have a more consistent XML output to convert to CSV.

ChristerH · April 10, 2024, 5:53am

I will try this, it seems better to clean up before transforming.

ChristerH · April 10, 2024, 12:26pm

Camt54_Test1.xml (38.2 KB)
It doesn’t seem to be the same format, I attached one of our files.

frameexpert · April 10, 2024, 12:51pm

I am doing some analysis on your XML file. You have 7 Ntry elements and 19 TxDtls elements. It seems like you would need a row in your CSV file for each TxDtls element, correct?

frameexpert · April 10, 2024, 1:08pm

There is definitely some variation in the data. Here is a snippet of the output:

<data>
   <entry>
      <Datum>2024-04-02</Datum>
      <Namn/>
      <Belopp/>
      <Valutakod/>
      <Referens/>
   </entry>
   <entry>
      <Datum>2024-04-02</Datum>
      <Namn>AKTIEBOLAGET STJÄRN-STANS</Namn>
      <Belopp>361.46</Belopp>
      <Valutakod>SEK</Valutakod>
      <Referens/>
   </entry>
   <entry>
      <Datum>2024-04-02</Datum>
      <Namn>AKTIEBOLAGET STJÄRN-STANS</Namn>
      <Belopp>733.00</Belopp>
      <Valutakod>SEK</Valutakod>
      <Referens/>
   </entry>
   <entry>
      <Datum>2024-04-02</Datum>
      <Namn>AKTIEBOLAGET STJÄRN-STANS</Namn>
      <Belopp>2430.95 3285.97</Belopp>
      <Valutakod>SEK SEK</Valutakod>
      <Referens/>
   </entry>
</data>

The second and third entries look correct.

The first entry has a different pattern for the TxDtls element:

<TxDtls>
  <AmtDtls>
    <TxAmt>
      <Amt Ccy="SEK">3348.00</Amt>
    </TxAmt>
  </AmtDtls>
  <RmtInf>
    <Ustrd>SEB TRYGGLIV</Ustrd>
  </RmtInf>
</TxDtls>

and that is why the elements are blank.

The last entry has two values for the amount and currency because it has a RmtInf element with two transactions in it:

<RmtInf>
  <Strd>
    <RfrdDocInf>
      <Tp>
        <CdOrPrtry>
          <Cd>CINV</Cd>
        </CdOrPrtry>
      </Tp>
      <Nb>544655947</Nb>
    </RfrdDocInf>
    <RfrdDocAmt>
      <RmtdAmt Ccy="SEK">2430.95</RmtdAmt>
    </RfrdDocAmt>
  </Strd>
  <Strd>
    <RfrdDocInf>
      <Tp>
        <CdOrPrtry>
          <Cd>CINV</Cd>
        </CdOrPrtry>
      </Tp>
      <Nb>544655145</Nb>
    </RfrdDocInf>
    <RfrdDocAmt>
      <RmtdAmt Ccy="SEK">3285.97</RmtdAmt>
    </RfrdDocAmt>
  </Strd>
</RmtInf>

Note that all of these issues can be dealt with with XSLT, but you have to know the patterns to look for.

ChristerH · April 11, 2024, 6:39am

I only want Ntfctn.Ntry.CdtDbtInd = CRDT in my output

The best output I got in EDT:
Camt54_Test1.csv (533 Bytes)

The transform I use:
CMT54_stack.transform (4.9 KB)