Qiflib

A ruby library for reading and parsing qif files.

Transaction parsing is supported, including address fields, and up to three “splits” per transaction. See the DDL, below, for the list of fields for each transaction.

Category-name parsing is also supported.

Also includes DDL generation for importing the parsed categories and transactions into a sqlite3 database.

Install

gem install qiflib

Synopsis

# Parse the transactions from one or more files to csv.
# Due to a discrepancy between the way iBank and Quicken export
# qif files, you need to specify either Qiflib::SOURCE_IBANK or
# Qiflib::SOURCE_QUICKEN as the :source.

input_list = []
input_list << { 
  :owner    => 'Chris',
  :filename => 'data/ibank_cj.qif',
  :source   => Qiflib::SOURCE_IBANK }
input_list << { 
  :owner    => 'Teri',
  :filename => 'data/ibank_tj.qif',
  :source   => Qiflib::SOURCE_QUICKEN }

csv_lines = Qiflib::Util.transactions_to_csv(input_list) 

... write csv_lines to a file

# Parse the transactions from one or more files to ^-delimited text. 
# The text lines are suitable for loading to sqlite3.

input_list = []
input_list << { 
  :owner    => 'Chris',
  :filename => 'data/ibank_cj.qif',
  :source   => Qiflib::SOURCE_IBANK }

delim_lines = Qiflib::Util.transactions_to_delim(input_list)

... write delim_lines to a file

# Parse the categories from one or more files to csv.

input_list = [ 'data/ibank_cj.qif' ]
csv_lines = Qiflib::Util.catetory_names_to_csv(input_list)

... write csv_lines to a file 

# Parse the categories from one or more files to ^-delimited text. 
# The text lines are suitable for loading to sqlite3. 

input_list = [ 'data/ibank_cj.qif' ]
delim_lines = Qiflib::Util.catetory_names_to_delim(input_list)

... write delim_lines to a file 

# Generate the DDL for a sqlite3 database with categories and transactions tables.

ddl_lines = Qiflib::Util.generate_sqlite_ddl

... write ddl_lines to a file; use it in the following bash-shell script  

# Generate a bash-shell script to load the categories and transactions 
# delimited files into a sqlite3 database, using the above DDL.

sh_lines = Qiflib::Util.generate_sqlite_load_script 

... write sh_lines to a file; chmod 744; run it.

Generated DDL

# DDL file for sqlite3

drop table if exists transactions;
drop table if exists categories;

create table transactions(
  id               integer,
  acct_owner       varchar(80),
  acct_name        varchar(80),
  acct_type        varchar(80),
  date             varchar(80),
  amount           real,
  number           varchar(80),
  ibank_n          varchar(80),
  cleared          varchar(80),
  payee            varchar(80),
  category         varchar(80),
  memo             varchar(80),
  split1_amount    real,
  split1_category  varchar(80),
  split1_memo      real,
  split2_amount    varchar(80),
  split2_category  varchar(80),
  split2_memo      varchar(80),
  split3_amount    real,
  split3_category  varchar(80),
  split3_memo      varchar(80),
  address1         varchar(80),
  address2         varchar(80),
  address3         varchar(80),
  address4         varchar(80),
  address5         varchar(80),
  address6         varchar(80),
  eol_ind          char(1)
);

create table categories(
  id       integer,
  name     varchar(80)
);

.separator '^'

.import qiflib_transactions.txt transactions
.import qiflib_categories.txt   categories

Generated shell script

# bash-shell script 

#!/bin/bash

sqlite3 qiflib.db < qiflib.ddl

Copyright and License

Copyright 2013, Chris Joakim <cjoakim@bellsouth.net>.

GNU General Public License (GPLv3) license. See www.gnu.org/copyleft/gpl.html