QuickETL = Quicken Extract Transform and Load

Version: 0.1.0 Date: 2008-02-10

QuickETL is used to parse your Quicken *.qif files into csv format, then load this csv data into a sqlite database, where it can be subsequently queried and used for reporting.

QuickETL functionality is invoked from a command line with the Ruby Rake program. File ‘rakefile.rb’ is included in the gem file.

Future versions of QuickETL will add a User Interface for running queries and generating reports.

General

Author: Chris Joakim <[email protected]>
Requires: Ruby 1.8.4 or later
License: Copyright 2008 by Chris Joakim. GNU General Public License (GPL) license. See www.gnu.org/copyleft/gpl.html

License

QuickETL is available under GNU General Public License (GPL) license.

Download

The latest version of QuickETL can be found at rubyforge.org/projects/quick-etl/

Installation

Please perform the following steps to install QuickETL.

Create a directory on your computer which will be considered the “home” directory for QuickETL. This directory will contain your Quicken *.qif files, as well as the following files:

- QuickenData.qif       (YOUR qif file, created by using the Quicken File -> Export... function)
- rakefile.rb           (The Ruby Rake "rakefile"; your interface to QuickETL functionality)      
- quick-etl.properties  (required but unused in this release)
- quick-etl.csv         (csv file created by parsing your *.qif file).
- quick-etl.db          (sqlite database file).
- quick-etl.load        (generated file used to load the csv file)

Create the environment variable QUICK_ETL_HOME, and set its value to the directory name you chose in step 1. This environment variable MUST be set for QuickETL to function properly.

Restart your shell program after setting this environment variable.
Verify that you have the sqlite3 program installed on your computer. See www.sqlite.org/ for sqlite3 installation and usage instructions.

For Max OS/X 10.4+ users, the sqlite3 is already installed with the OS.
QuickETL is packaged and installed as a ruby ‘gem’. Download the latest gem to your computer, then run the following command from your download directory.

gem install quick-etl-1.0.0.gem
Unpack the ‘quick-etl-0.1.0.gem’ file in your download directory with the following command:

gem unpack quick-etl-0.1.0.gem

Then copy the ‘rakefile.rb’ and ‘quick-etl.properties’ files, that were unpacked from the gem, to your QUICK_ETL_HOME directory.

Usage

Use your Quicken program to export its data to the QUICK_ETL_HOME directory. Specify output file ‘QuickenData.qif’.
Open a shell window in your QUICK_ETL_HOME directory.

Execute command ‘rake -T’ to see the list of functions available.

Execute command ‘rake parse’ to parse the qif file into csv format.

Execute command ‘rake load’ to load the csv file data into sqlite3 database ‘quick-etl.db’.

Execute command ‘rake parse_and_load’ to run both the parsing and loading function.

Execute command ‘rake show_ddl’ to see the structure of the database table and its column names.
Use the sqlite3 CLP (Command Line Program) to query the database. From the QUICK_ETL_HOME directory, execute command ‘sqlite3 quick-etl.db’.

You can then enter and execute queries like the following:

sqlite> select * from transactions; sqlite> select * from transactions where tran_date == ‘2008-02-05’;

Note: A User Interface will be created for QuickETL its next release.

Road Map / TODO List

Develop a UI for the quick-etl.db sqlite database.
Develop reporting functionality.
Support the “merging” of multiple *qif files into one database.

Support

Please see rubyforge.org/projects/quick-etl/ to submit a request or report a bug, on the Tracker page.

Warranty

This software is provided “as is” and without any express or implied warranties, including, without limitation, the implied warranties of merchantibility and fitness for a particular purpose.