Qiflib

version 0.5.0, released 12-14-2013

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.

Version 0.5.0 includes support for iBank version 4.7.5, this version exports silightly differently than previous versions.

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),
  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 private/qiflib_transactions.txt transactions
.import private/qiflib_categories.txt   categories

CSV fields

csv field index 0 = id
csv field index 1 = acct_owner
csv field index 2 = acct_name
csv field index 3 = acct_type
csv field index 4 = date
csv field index 5 = amount
csv field index 6 = number
csv field index 7 = cleared
csv field index 8 = payee
csv field index 9 = category
csv field index 10 = memo
csv field index 11 = split1_amount
csv field index 12 = split1_category
csv field index 13 = split1_memo
csv field index 14 = split2_amount
csv field index 15 = split2_category
csv field index 16 = split2_memo
csv field index 17 = split3_amount
csv field index 18 = split3_category
csv field index 19 = split3_memo
csv field index 20 = address1
csv field index 21 = address2
csv field index 22 = address3
csv field index 23 = address4
csv field index 24 = address5
csv field index 25 = address6
csv field index 26 = eol_ind

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