Sunday, September 26, 2010

Converting QIF to TEXT

My bank only outputs my account history's in several propriety formats that require I must buy some commercial software to view. When in reality I just want something quick in dirty to make in to a simple text file that I can grep through, and say search for all my trips to fast food or list out the checks cashed. 

One format is QIF and I did a quick and dirty little sed script to turn that in to a simple list in plain text one line per entry. 
See:  Quicken Interchange Format (QIF)

So I can turn this:

MWithdrawal Debit Card Debit Card

Which is really one never ending mess on a huge single line in notepad

In to this:
04/26/2010 -11.81 MCDONALD'S F21823 GOLETA CA Withdrawal Debit Card Debit Card

Below is a little shell script that used the unix command sed to clean this up.

sed \
-e '/^!/d' \
-e 's/\^/ NEWLINE/' \
-e 's/^.//' $1 | \
sed -e ':a;N;$!ba;s/\n/ /g' | \
sed -e 's/NEWLINE/\n/g'

One you have that you can quickly use grep to filter out specific entries and then sum then with awk.

./qif2text  myaccounthist.qif | grep Draft | awk '{ SUM += $2} END { print SUM }'

1 comment:

Anonymous said...

Thanks for this! I'm hoping to import Quicken into MySQL and this looks like it will help greatly.