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

qb2ledger

#! /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