version 0.4.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.4.0 includes support for iBank version 4.7.5, this version exports silightly differently than previous versions.
gem install qiflib
# 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.
# 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 private/qiflib_transactions.txt transactions .import private/qiflib_categories.txt categories
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 = ibank_n csv field index 8 = cleared csv field index 9 = payee csv field index 10 = category csv field index 11 = memo csv field index 12 = split1_amount csv field index 13 = split1_category csv field index 14 = split1_memo csv field index 15 = split2_amount csv field index 16 = split2_category csv field index 17 = split2_memo csv field index 18 = split3_amount csv field index 19 = split3_category csv field index 20 = split3_memo csv field index 21 = address1 csv field index 22 = address2 csv field index 23 = address3 csv field index 24 = address4 csv field index 25 = address5 csv field index 26 = address6 csv field index 27 = eol_ind
# bash-shell script #!/bin/bash sqlite3 qiflib.db < qiflib.ddl
Copyright 2013, Chris Joakim <cjoakim@bellsouth.net>.
GNU General Public License (GPLv3) license. See www.gnu.org/copyleft/gpl.html