qiflib

== Qiflib

version 0.6.0, released 12-15-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.

Qiflib now includes support for iBank version 4.7.5; this version exports qif-files slightly 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), end_balance 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 = end_balance csv field index 27 = eol_ind

== Generated shell script

bash-shell script

#!/bin/bash

sqlite3 qiflib.db < qiflib.ddl

== Copyright and License

Copyright 2013, Chris Joakim [e-mail].

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