aib2xero

aib2xero.rb

#!/usr/bin/env ruby

#  Turns an AIB CSV export in to a Xero friendly import.
# This script is quick and dirty, feel free to DRY it up and improve....

# Xero likes: https://central.xero.com/s/article/Import-a-CSV-bank-statement
# Date, Amount (-/+ prefix), Payee, Description, Reference, Cheque Number, Analysis Code, Transaction Type

# AIB : IB(Internet Banking) : Historical 
# Posted Account, Posted Transactions Date, Description1, Description2, Description3, Debit Amount, Credit Amount,Balance,Posted Currency,Transaction Type,Local Currency Amount,Local Currency

# AIB : IB(Internet Banking) : Current
# Posted Account, Posted Transactions Date, Description, Debit Amount, Credit Amount,Balance,Transaction Type

# ===================================================================================================
# Note: DO NOT USE THIS ON the historical export rather use on THE CURRENT ACCOUNT VIEW EXPORT
# ===================================================================================================

# Usage ./aib2xero.rb <aib_current_account_export.csv>
# and it creates a ready to use import for Xero

require "csv"
require 'fileutils'

out_file = File.new("ready_for_xero_import_"+Time.now.strftime("%Y%m%dT%H%M%S")+".csv", "w")
exclude_file = File.new("excluding_"+Time.now.strftime("%Y%m%dT%H%M%S")+".csv", "w")

header = "*date, *amount, payee, description, reference, cheque number,analysis code, transaction type"
out_file.puts(header)

CSV.foreach(ARGV.first, :headers => true) do |row|
  if row[6].nil? || row[6].empty?
    exclude_file.puts(row)
    next
  end
  posted_account            = row[0].tr(',', '').strip.to_s if row[0]
  posted_transactions_date  = row[1].tr(',', '').strip.to_s if row[1]
  description_1             = row[2].tr(',', '').strip.to_s if row[2]
  debit_amount              = row[3].tr(',', '').strip.to_s if row[3]
  credit_amount             = row[4].tr(',', '').strip.to_s if row[4]
  balance                   = row[5].tr(',', '').strip.to_s if row[5]
  transaction_type = row[6].strip.to_s.downcase if row[6]

  if transaction_type && transaction_type.downcase.include?("credit")
    prefix = "+"
  else
    prefix = "-"
  end

  if !credit_amount.nil? && !credit_amount.empty?
    amount = credit_amount.to_s
  elsif !debit_amount.nil? && !debit_amount.empty?
    amount = debit_amount.to_s
  end

  #if description_3 && description_3.empty?
  #  spacer = ""
  #else
  #  spacer = " with "
  #end
  posted_account ||= ""
  posted_transactions_date ||= ""
  #local_currency_amount ||= ""
  description_1 ||= "bad_description"
  amount ||= "unknown_amount"
  prefix ||= "bad_prefix"
  transaction_type ||= "bad transaction_type"
  transaction_line = posted_transactions_date+","+prefix.to_s+amount.to_s+",,"+description_1+",,,,"+transaction_type.to_s
  out_file.puts(transaction_line)
end
out_file.close
exclude_file.close
puts "\n"
puts "Below is the contents of the new *import* file and there is also an exclude file for the removed content (please check both!):\n"
puts "\n"

print_contents = File.readlines(out_file).each do |line|
  puts line
end

exit
__END__