Convert CSV files into formatted CSV or QIF (OFX support might be in future)

smbc_convert.rb

#!/usr/bin/env ruby

# gist: https://gist.github.com/akisute/1976286e8cee7703dd7d
#
# Needs QIF and OFX support
# QIF gem - https://rubygems.org/gems/qif
# OFX gem - https://rubygems.org/gems/ofx
#         - https://rubygems.org/gems/ofx-parser
# QIF format doc - http://en.wikipedia.org/wiki/Quicken_Interchange_Format
# OFX format doc - http://www.exactsoftware.com/docs/DocView.aspx?DocumentID=%7B6e02f9a5-ee40-4d2f-b8ea-4bee57825907%7D
# TODO: OFX is not supported yet.

require 'csv'
require 'date'
require 'pp'
require 'qif'

FORMAT_TYPES = [:settled, :upcoming]

class Line
  attr_accessor :date
  attr_accessor :date_clear
  attr_accessor :cleared
  attr_accessor :payee
  attr_accessor :amount
  
  def initialize(row, format_type, csv_filename)
    if format_type == :settled
      @date = row[0]
      if match = csv_filename.match(/([0-9]{4})([0-9]{2})\.csv/)
        @date_clear = "#{match[1]}/#{match[2]}/10"
      else
        raise ArgumentError("Invalid csv_filename #{csv_filename}")
      end
      @cleared = true
      @payee = row[1]
      @amount = -(row[5].to_i)
    elsif format_type == :upcoming
      @date = row[0]
      @date_clear = row[5].to_s.gsub!(/'(.+)/, '20\1/10')
      @cleared = false
      @payee = row[1]
      @amount = -(row[6].to_i)
    else
      raise ArgumentError("Invalid format_type #{format_type}")
    end
  end
end

def ext_filename(format_type, ext)
  "smbc_#{format_type.to_s}.#{ext}"
end

def export_csv(filename)
  format_type = nil
  open(filename, "rb:Shift_JIS:UTF-8", undef: :replace) do |f|
    csv = CSV.new(f)
    begin
      Date.parse(csv.readline[0].to_s)
      format_type = :upcoming
      csv.rewind
    rescue ArgumentError
      format_type = :settled
    end
  
    open(ext_filename(format_type, "csv"), "wb:Shift_JIS", undef: :replace) do |f|
      ext_csv = CSV.new(f)
      prev_date = ""
      csv.each do |row|
        if not row[1].to_s.empty?
          if row[0].to_s.empty?
            row[0] = prev_date
          else
            prev_date = row[0]
          end
          if format_type == :upcoming
            row[5].to_s.gsub!(/'(.+)/, '20\1/10')
          end
          ext_csv << row
        end
      end
    end
  end
end

def export_qif(filename)
  lines = []
  format_type = nil
  open(filename, "rb:Shift_JIS:UTF-8", undef: :replace) do |f|
    csv = CSV.new(f)
    begin
      Date.parse(csv.readline[0].to_s)
      format_type = :upcoming
      csv.rewind
    rescue ArgumentError
      format_type = :settled
    end
    
    prev_date = ""
    csv.each do |row|
      if not row[1].to_s.empty?
        if row[0].to_s.empty?
          row[0] = prev_date
        else
          prev_date = row[0]
        end
        lines << Line.new(row, format_type, filename)
      end
    end
  end
  
  Qif::Writer.open(ext_filename(format_type, "qif"), type="Bank", format="yyyy/mm/dd") do |qif|
    lines.each do |line|
      qif << Qif::Transaction.new(
      :date => line.date,
      :amount => line.amount,
      :payee => line.payee,
      :status => line.cleared ? "X" : "",
      )
    end
  end
end

def export_ofx(filename)
end


if __FILE__ == $0
  filename = ARGV[0]
  exit if filename.to_s.empty?
  
  #export_csv(filename)
  export_qif(filename)
end