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:

^
D04/26/2010
T-11.81 PMCDONALD'S F21823 GOLETA CA
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.

qif2text
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.