I would like to transform the monthly bank statements into Excel files so I can work on them. They are XML files created according to the camt053 format.
When I open such a file with EDT it says 1,2k cols x 1row, which can’t be right.
Any suggestions on how to do this correctly?
Yes! That did the trick. 91 cols x 201 rows now and the result looks good. Thank you.
I just started using EDT. Now I have to figure out how to create a repeatable job of it, automatically excluding many of the columns that aren’t relevant to what I want to do with the data. Is that possible and, if yes, is there already an instruction text somewhere about how to do this?
Use the Remove Cols transform to remove the columns you don’t need.
You can use the same .transform for lots of input files.
If you want to do them one at a time, just change the file the input item points to.
If you want to do it for lots of files use the batch feature:
Or command line arguments:
Thank you for the instructions. Have used the remove cols transform to do a first step.
For some reason, all movements are list 3 times in 3 sequential rows in the statement.
Flattening an XML file into a table can result in duplicate or near duplicate rows. You can remove or condense these using Dedupe or Unique transforms.
Thank you for your detailed replies.
I will continue experimenting with these camt053 files, since I couldn’t find anything on the Internet to help with that. The less expensive accounting tools for personal use don’t import them (yet?). Only the older formats like qif, ofx, qfx and mt940. And the banks in Europe are adopting this new format from ISO 2022, as far as I could recon from my searches. My bank in Switzerland has adopted it and makes the monthly statements available in this format and in pdf. And I would like to automate the import to MoneyWiz as much as possible.
Maybe there are more people out there with the same difficulties, and you could find more customers for EDT if you add some specific information about this to your advertising texts.
@ CristianoR You aren’t the first person to ask about camt053. I don’t know much about this format and probably ought to look into.
If you can share a sample file and the output you wish for, some one maybe can come up with the solution, without any sample and expected output, it’s difficult to provide any hints or suggestion.
You could simply scramble the data in one of your xml files and just write down the columns that you want and in order like for example
Expected Output
col1,col2,col3,col4
123,1253,some text,432123
col1… are the columns names that you are interested
123,… are the values for the respected columns.
I used the Remove Cols transform as suggested by the Admin and that worked well.
I had a look online for a sample camt.053 statement I could download, but I didn’t find anything yet.
I did a search using DEVONagent Pro and it showed me this page. Is it helpful?
I also noticed that this question in the forum showed up in the search results. Maybe other people searching for answers to this subject will land here as well.
We need example files, rather than a parser. However most real camt.x files are going to be rather sensitive, so I am not sure people will want to share them.
This is my case. Can I delete or change all sensitive data from one of my files before sending it to you, without “damaging” it for your analysis?
Yes, just replace any sensitive data with something random of the same type (number/string/ date etc).
What tool do you recommend to do that (for a Mac, if possible)?
EDT to transform the xml file to an edited xml file?
You don’t want to use anything that might change the format. If it isn’t a long file, you could just edit it with an text editor.
OK. I will have a look at it and send the file through email. Which email address should I use?
If someone else is going to analyse and edit xml files, I suggest using an app for programmers like Sublime Text. They show the text in different colours for different parts, which makes it much easier to work with. Normal text editors just show one very large sequence of texts and symbols in one colour.
