Create a gist now

Instantly share code, notes, and snippets.

Converts a QuickBooks General Journal CSV export to a ledger_cli compatible transaction file. More details at http://www.timetiger.com/gene/blog/2012/2012-09-23-an-alternative-to-quickbooks.html
#! /usr/bin/env ruby
require 'csv'
require 'Date'
require 'optparse'
SCRIPT_VERSION = [1, 0, 0]
class Transaction
attr_accessor :transaction_id, :type, :date, :num, :rows
def initialize
@transaction_id = ""
@type = ""
@date = nil
@num = nil
@rows = Array.new
end
def parse_header(row)
@transaction_id = row[0].to_i
@type = row[1]
@date = Date.parse(row[2])
@num = row[3]
end
def best_memo
# The best memo is defined as the memo or name of the first row that has one
@rows.each { |row|
if row.memo.to_s.length > 0
return row.memo
end
if row.name.to_s.length > 0
return row.name
end
}
# Return an empty string if we haven't been able to find a memo
return ""
end
def output
memo = best_memo
print @date.to_s + " "
if not (@num.nil?)
print "(" + @num.to_s + ") "
end
print memo + "\n"
@rows.each { |row|
print "\t" + row.account.to_s + "\t"
if (row.debit.to_f != 0.0)
print row.debit
elsif (row.credit.to_f != 0.0)
print "-" + row.credit
end
# If this row has a memo that differs from our best memo,
# include it as a comment
if row.memo.to_s.length > 0 and row.memo.to_s != best_memo
print "\t; " + row.memo.to_s
end
print "\n"
}
print "\n"
end
end
class TransactionRow
attr_accessor :num, :name, :memo, :account, :debit, :credit
def initialize
@name = ""
@memo = ""
@account = ""
@debit = ""
@credit = ""
end
def parse_row(row)
@num = row[3]
@name = row[4]
@memo = row[5]
@account = row[6]
@debit = row[7]
@credit = row[8]
end
def total_row?
if (@debit == @credit) && (@account.nil?)
return true
end
return false
end
def blank_row?
if (@debit.to_f == 0.0) and (@credit.to_f == 0.0)
return true
end
return false
end
end
# Holds the command-line options
options = {}
optparse = OptionParser.new do |opts|
opts.banner = "Usage: qb2ledger [options] FILE"
opts.on('-h', '--help', 'Display this screen') do
puts opts
exit
end
opts.on('--version', 'Show version') do
puts SCRIPT_VERSION.join('.')
exit
end
end
optparse.parse!
csv_source_file = ARGV[0] or abort(optparse.help())
csv_text = File.read(csv_source_file)
csv = CSV.parse(csv_text)
firstRow = true
current_transaction = nil
csv.each do |row|
if firstRow
firstRow = false
else
id = row[0].to_i
if (id == 0)
# Continuation from previous transaction
else
# Record the previous transaction, if any
if (!current_transaction.nil?)
current_transaction.output
end
# Start a new transaction
current_transaction = Transaction.new
current_transaction.parse_header(row)
end
# Add a row for the transaction if it isn't a total row or blank
transaction_row = TransactionRow.new
transaction_row.parse_row(row)
if (not transaction_row.total_row?) and (not transaction_row.blank_row?)
current_transaction.rows << transaction_row
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment