CSV to IIF

Several folks have written asking about the CSV-to-IIF script that I mentioned in this column. It's a humble thing, and likely won't work directly with your bank's output format, but if it's a helpful example I'm happy to share.

# Script to convert CSV to IIF output. I run it like so:
#
# python qb.py > output.iif
 
import sys,traceback,re
  
def error(trans):
  sys.stderr.write("%s\n" % trans)
  traceback.print_exc(None,sys.stderr)
  
# This is the CSV format that my bank emits. Yours likely differs.
# I copy the CSV data from the bank's export page, and paste it here 
# between the triple quotes.
 
csv = """
4/30/2006,-12.93,"","","EASTERN PROPANE GAS"
4/30/2006,-28.94,"","","AMZ*SUPERSTORE AMAZON .COM WA"
"""
  
# This is the name of the QuickBooks checking account
  
account = "Walpole Checking"
  
# This is the IIF template
  
template = """!TRNS\tTRNSID\tTRNSTYPE\tDATE\tACCNT\tNAME\tAMOUNT\
\tDOCNUM\tADDR1\tADDR2\tADDR3\tMEMO
!SPL\tSPLID\tACCNT\tAMOUNT\tMEMO
!ENDTRNS
TRNS\t\tCheck\t%s\t%s\t%s\t%s\t%s\t\t\t\t
SPL\t\tUncategorized Expenses\t%s
ENDTRNS"""
 
# And here's the part that squirts the data through the template
 
lines = csv.split('\n')
 
for trans in lines[1:-1]:
 
  try:
    list = trans.split(',')
    assert (len(list)==5 )
  except:
    error(trans)
    continue
 
  try:
    (date,amount,serial,descr,comments) = list
    m = re.search('(\d{1,2})/(\d{1,2})/(\d{4})', date)
    assert ( len(m.groups())==3 )
  except:
    error(trans)
    continue
 
  try:
    amount = float(amount)
  except:
    error(trans)
    continue
 
  serial = serial.strip('"')
  if ( serial == ''):
    serial = 'visa'
 
  descr = descr.strip('"')
 
  comments = comments.strip('"')
  if ( comments == ''):
    comments = descr
  comments = comments.replace('ATM POS Debit/','')
  comments = comments.replace('ACH Withdrawal/','')
 
  print template % (date,account,comments,amount,serial,-(amount) )

Former URL: http://weblog.infoworld.com/udell/2006/04/22.html#a1433