A QuickBooks list importer
[Posted August 7, 2017 by corbet]
#!/usr/bin/python
#
# A simple QuickBooks list importer.
#
# See https://lwn.net/Articles/729087/ for the article that describes
# this program.
#
# Copyright 2017 Jonathan Corbet.
# This program may be distributed under the terms of the GNU General
# Public License, version 2 or later.
#
# This program is provided with no warranty of any kind. You, and only
# you, are responsible for the integrity of your accounting data.
#
import sys, re, argparse, os
import iif
import remap
import gnucash
from gnucash import gnucash_business, gnucash_core_c
#
# Map QB account types to GC
#
AccountTypeMap = {
'BANK': gnucash.ACCT_TYPE_BANK,
'OCASSET': gnucash.ACCT_TYPE_ASSET,
'OASSET': gnucash.ACCT_TYPE_ASSET,
'FIXASSET': gnucash.ACCT_TYPE_ASSET,
'AP': gnucash.ACCT_TYPE_PAYABLE,
'AR': gnucash.ACCT_TYPE_RECEIVABLE,
'OCLIAB': gnucash.ACCT_TYPE_LIABILITY,
'LTLIAB': gnucash.ACCT_TYPE_LIABILITY,
'EQUITY': gnucash.ACCT_TYPE_EQUITY,
'INC': gnucash.ACCT_TYPE_INCOME,
'EXP': gnucash.ACCT_TYPE_EXPENSE,
'EXINC': gnucash.ACCT_TYPE_INCOME,
'EXEXP': gnucash.ACCT_TYPE_EXPENSE,
}
def lookup_acct_type(type):
try:
return AccountTypeMap[type]
except KeyError:
print 'Unknown QB account type:', type
return gnucash.ACCT_TYPE_EXPENSE # Not sure what else to do
#
# Stuff for reparenting accounts into the GnuCash scheme.
#
AccountRoots = {}
def root_map(type, root, root_type = None):
if not root_type:
root_type = type
AccountRoots[type] = (root, type)
root_map(gnucash.ACCT_TYPE_ASSET, 'Assets')
root_map(gnucash.ACCT_TYPE_BANK, 'Assets', gnucash.ACCT_TYPE_EQUITY)
root_map(gnucash.ACCT_TYPE_EQUITY, 'Equity')
root_map(gnucash.ACCT_TYPE_EXPENSE, 'Expenses')
root_map(gnucash.ACCT_TYPE_INCOME, 'Income')
root_map(gnucash.ACCT_TYPE_LIABILITY, 'Liabilities')
root_map(gnucash.ACCT_TYPE_PAYABLE, 'Liabilities', gnucash.ACCT_TYPE_LIABILITY)
root_map(gnucash.ACCT_TYPE_RECEIVABLE, 'Assets', gnucash.ACCT_TYPE_EQUITY)
# This is not complete of course...
def find_root(root, atype):
if not Reparent:
return root
try:
rootname, rtype = AccountRoots[atype]
except KeyError:
return root
#
# Make sure this account exists.
#
subroot = root.lookup_by_name(rootname)
if subroot:
return subroot
#
# Nope, gotta create it.
#
subroot = gnucash.Account(book)
subroot.BeginEdit()
subroot.SetType(rtype)
subroot.SetName(rootname)
subroot.SetCommodity(dollars)
root.append_child(subroot)
subroot.CommitEdit()
return subroot
#
# Transform account names. For now, just remove slashes, since they
# make GnuCash confused and unhappy.
#
def fixup_acct_name(name):
return name.replace('/', '')
#
# Walk through the hierarchy to find the right home for this account.
#
def find_parent(name, root):
sname = name.split(':')
parent = root
for acct in sname[:-1]:
acct = fixup_acct_name(acct)
parent = parent.lookup_by_name(acct)
if not parent:
print 'Failed to find container account', acct
return root, sname[-1]
return parent, sname[-1]
#
# We've been given an account name with slashes in it; assume it's already
# in the GnuCash account space and resolve it accordingly. Create the
# intermediate names if needed.
#
def GC_path(root, name, atype):
path = name.split('/')
for dir in path[:-1]:
new = root.lookup_by_name(dir)
if not new:
new = gnucash.Account(book)
new.BeginEdit()
new.SetType(atype)
new.SetName(dir)
new.SetCommodity(dollars)
root.append_child(new)
new.CommitEdit()
root = new
return (new, path[-1])
#
# Import a list of accounts.
#
def import_accounts(list):
root = book.get_root_account()
for entry in list.entries:
#
# Figure out the type and do remapping/reparenting.
#
atype = lookup_acct_type(entry['ACCNTTYPE'])
name = remap.remap(fixup_acct_name(entry['NAME']))
if name.find('/') >= 0:
parent, name = GC_path(root, name, atype)
else:
parent, name = find_parent(name, find_root(root, atype))
#
# Don't make an account that already exists.
#
if parent.lookup_by_name(name):
print 'Account %s already exists' % (name)
continue
#
# Make the account.
#
acct = gnucash.Account(book)
acct.BeginEdit()
acct.SetType(atype)
acct.SetName(name)
acct.SetCommodity(dollars)
parent.append_child(acct)
acct.CommitEdit()
#
# Import the vendor list.
#
def import_vendors(vlist):
for ventry in vlist.entries:
name = fix_vname(ventry['NAME'])
vendor = gnucash_business.Vendor(book = book,
id = book.VendorNextID(),
currency = dollars,
name = name)
vendor.BeginEdit()
addr = vendor.GetAddr()
addr.BeginEdit()
addr.SetName(ventry['PRINTAS'] or name)
addr.SetAddr1(ventry['ADDR1'])
addr.SetAddr2(ventry['ADDR2'])
addr.SetAddr3(ventry['ADDR3'])
addr.SetPhone(ventry['PHONE1'])
addr.CommitEdit()
if ventry['TAXID']:
inst = vendor.get_instance()
gnucash_core_c.gncVendorSetNotes(inst, ventry['TAXID'])
vendor.CommitEdit()
#
# LWN specific: QB forced the addition of a string to distinguish
# vendors from the other names list, but we don't need it here.
#
asuffix = re.compile(r', +author', re.I)
def fix_vname(name):
return asuffix.sub('', name)
#
# Import the customer list.
#
def import_customers(clist):
for centry in clist.entries:
cust = gnucash_business.Customer(book = book,
id = book.CustomerNextID(),
currency = dollars,
name = centry['NAME'])
cust.BeginEdit()
addr = cust.GetAddr()
addr.BeginEdit()
addr.SetName(centry['BADDR1'])
addr.SetAddr1(centry['BADDR2'])
addr.SetAddr2(centry['BADDR3'])
addr.SetAddr3(centry['BADDR4'])
addr.SetAddr4(centry['BADDR5'])
addr.SetPhone(centry['PHONE1'])
addr.CommitEdit()
cust.CommitEdit()
#
# Here we do the argparsery
#
def setupargs():
p = argparse.ArgumentParser()
p.add_argument('-c', '--create', required = False, default = False,
action = 'store_true', help = 'Create a new gnucash file')
p.add_argument('-m', '--mapfile', required = False, default = None,
help = 'Name of account-name remapping file')
p.add_argument('-o', '--override', required = False, action = 'store_true',
help = 'Override lock on the gnucash file')
p.add_argument('-r', '--reparent', required = False, action = 'store_true',
help = 'Reparent accounts gnucash-style')
p.add_argument('iif', help = 'The IIF file to process')
p.add_argument('gcf', help = 'The gnucash file to import into')
return p
def croak(message):
sys.stderr.write(message)
sys.stderr.flush()
sys.exit(1)
#
# Main program time.
#
#
# Check args and open files
#
args = setupargs().parse_args()
Reparent = args.reparent
try:
lists = iif.read_IIF(open(args.iif, 'r'))
except IOError, e:
croak('Unable to open %s, %s' % (args.iif, e))
#
# Open the session. But if we're creating the file, we have to give it
# a full path or it barfs at us.
#
filename = args.gcf
if args.create:
filename = 'xml://%s/%s' % (os.getcwd(), filename)
session = gnucash.Session(filename, ignore_lock = args.override,
is_new = args.create)
#
# Load the mapfile if there is one.
#
if args.mapfile:
remap.load_mapfile(args.mapfile)
#
# Extract some useful stuff from the GC session
#
book = session.book
ctable = book.get_table() # Commodity lookup table
# All the world's the US, right??
dollars = ctable.lookup('CURRENCY', 'USD')
#
# Import the lists we understand.
#
Ilist = [ ('ACCNT', import_accounts),
('CUST', import_customers),
('VEND', import_vendors) ]
for name, importer in Ilist:
try:
importer(lists[name])
except KeyError:
pass
#
# Clean up and we be done.
#
session.save()
session.end()
sys.exit(0)
