Thursday, June 12, 2014

CSV to QIF transformation with Python3

This evening I discovered that the folks at my bank, or more probably the folks running the online banking website, have "upgraded" the user interface, and they no longer support downloading transactions to the "old" Quicken file format (i.e., "QIF"). Instead, they (mostly) support "export"ing transactions to CSV.

Fortunately, a format conversion from CSV to QIF isn't that hard. I first did a web search, which yielded

  • a website (upload your csv and we'll give you a qif file),
  • some Windows (I guess) software--free to download and try, but I guess they want money for it, and
  • maybe something else that I didn't think would work for me
so I decided to just hack something together. Of course it would be in Python, and probably python3.

I remembered that there was a module that knew how to read and write CSV files; a quick look at the library reference showed that the module is called, oddly enough, "csv"; the result is described here (produced by "pydoc3 -w csv_to_qif")

 
 
csv_to_qif
index
/mnt/home/collin/projects/csv-qif/csv_to_qif.py

transform a "csv" file on stdin to a "qif" file on stdout.

 
Modules
       
csv
os
sys

 
Functions
       
main(infile, outfile)
Read infile -- we expect valid entries to have 9 fields:
xaction#, date, descr, memo, amt debit, amt credit, bal, check#, fees
 
Write an outfile that looks like...
    !Type:Bank
    C*
    Dmm/dd/yyyy
    T<amount> (negative for withdrawal)
    N###      (## = checkNumber)
    P<descr>  (description...)
    ^
Each entry begins with "C" and ends with "^".
Here's the code. It is Copyright©2014 by me, but you are free to use, redistribute and or modify it under the terms of GPL2; see below.
#!/usr/bin/python3 -utt
# vim:et
'''transform a "csv" file on stdin to a "qif" file on stdout.'''

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, version 2.
# NO WARRANTY express or implied.

import csv
import os
import sys

def main(infile, outfile):
    '''Read infile -- we expect valid entries to have 9 fields:
    xaction#, date, descr, memo, amt debit, amt credit, bal, check#, fees
    
    Write an outfile that looks like...
        !Type:Bank
        C*
        Dmm/dd/yyyy
        T (negative for withdrawal)
        N###      (## = checkNumber)
        P  (description...)
        ^
    Each entry begins with "C" and ends with "^".'''

    outfile.write('!Type:Bank\n')

    reader = csv.reader(infile)
        
    for arow in reader:
        if len(arow) == 1:
            continue                    # a couple of title lines
        if len(arow) != 9:
            print("I don't grok this row with len=%d:\n\t'%s'" %
                        (len(arow), "',".join(arow)), outfile=sys.stderr)
            continue
        trans_num, date, descr, memo, deb, cred, bal, cnum, fees = arow
        if date.startswith('Date'):
            continue                    # that's the header line
        outfile.write('C*\n')
        outfile.write('D%s\n' % date)
        if deb:
            outfile.write('T%s\n' % deb)
        else:
            outfile.write('T%s\n' % cred)
        if cnum:
            outfile.write('N%s\n' % cnum)
        outfile.write('P%s\n' % descr)
        outfile.write('^\n')
    sys.exit(0)

if __name__ == '__main__':
    try:
        infile = open(sys.argv[1], 'r')
        outfile = open(sys.argv[2], 'w')
    except:
        print('usage: %s INFILE OUTFILE' % os.path.basename(sys.argv[0]))
    main(infile, outfile)
UPDATE 2014-06-24: Added a "C*" at the beginning of each entry. Without this, my quicken "check register" wasn't showing the "checks" as being Cleared.

No comments: