Convert ABN CAMT.053 ZIP file to ING semicolon separated file

abn-camt053-to-ing-csv.rb

require 'nokogiri'
require 'zip'

if ARGV.length != 1
  puts "Usage: ruby #{File.basename(__FILE__)} ABN-CAMT053.zip"
  exit
end

xml_files_content = Zip::ZipFile.open(ARGV.first) do |zip|
  zip.map do |entry|
    next if !entry.file? || entry.name !~ /\.xml$/
    entry.get_input_stream.read
  end.compact
end

result_lines = []
xml_files_content.each do |xml_file_content|
  doc = Nokogiri::XML(xml_file_content)
  doc.remove_namespaces!

  statement = doc.at_xpath("/Document/BkToCstmrStmt/Stmt")
  iban1 = statement.at_xpath("Acct/Id/IBAN").text
  fail("No iban1 available") if iban1.empty?

  statement.xpath("Ntry").each do |entry|

    date = entry.at_xpath("ValDt/Dt").text
    fail("No date available") if date.empty?

    amount_elem = entry.at_xpath("Amt")
    amount = amount_elem.text
    fail("No amount available") if amount.empty?
    fail("Unsupported currency encountered") if amount_elem.attributes["Ccy"].value != "EUR"

    abn_sign = entry.at_xpath("CdtDbtInd").text
    sign = {"CRDT" => "Bij", "DBIT" => "Af"}[abn_sign]
    fail("Unsupported sign encountered") if !sign

    details = entry.at_xpath("NtryDtls/TxDtls")
    description, iban2, comment = "", "", ""

    if details
      if sign == "Bij"
        description = details.at_xpath("RltdPties/Dbtr/Nm").text
        iban2 = details.at_xpath("RltdPties/DbtrAcct/Id/IBAN").text
      else
        description = details.at_xpath("RltdPties/Cdtr/Nm").text
        iban2 = details.at_xpath("RltdPties/CdtrAcct/Id/IBAN").text
      end

      fail("No counterparty description available") if description.empty?
      fail("No counterparty IBAN available") if iban2.empty?

      comment_node = details.at_xpath("RmtInf/Ustrd")
      comment = comment_node ? comment_node.text : ""
    else
      abn_stupidity = entry.at_xpath("AddtlNtryInf").text
      fail("No ABN stupidity available") if abn_stupidity.empty?

      card_transaction_regex = /\d\d\.\d\d\.\d\d\/\d\d\.\d\d/
      if abn_stupidity =~ card_transaction_regex
        description = abn_stupidity.split(card_transaction_regex).last.split(/,PAS\d\d\d/).first
      else
        description = abn_stupidity.split(/ {3,}/).first
      end

      comment = abn_stupidity
    end

    safe_description = description.gsub(";", ",")
    safe_comment = comment.gsub(";", ",")
    date_without_dashes = date.gsub("-", "")

    result_lines << "#{date_without_dashes};#{safe_description};#{iban1};#{iban2};;#{sign};#{amount};;#{safe_comment};;"
  end
end

puts "date;description;iban1;iban2;code;sign;amount;type;comment;balance;tag"
result_lines.sort.each do |result_line|
  puts result_line
end