Another way to export data

[Quentin Stafford-Fraser]

Hello all, 

As a new user, I've been impressed with the ease of getting data into GnuCash and a little concerned at the complexity of getting data out.  Advice that I should do things like copy and paste parts of an HTML page into a spreadsheet don't seem like ideal solutions to me, if, for example, my accountant wants CSV files on a regular basis.

Now, it's XML underneath so if I wanted to work out the relationships between the various interlinked tables I could write some code to do this.  Or I could use the Python bindings... if I can work out how to get them going on my Mac.  Mmm...

But then I remembered an easier way to get data out with a great deal of flexibility, and I thought I'd mention it in case others hadn't come across it.

John Wiegley's 'Ledger' program (http://ledger-cli.org/) is intended for those who want to keep their transactions in an easy plain-text format.  You just create a list of them in text files, and then run the utility to produce reports, balance sheets, budgets etc.  All very cool, but entering stuff in an editor can be very tedious which is why I switched to GnuCash.

However…

Ledger can read GnuCash files, as an alternative to its own text format.  At least, it can if:

a) You use Ledger 2.x - The GnuCash support has not yet been ported to the newer Ledger 3.0.

b) Your GnuCash file is not compressed.  So you either need to turn off compression in your preferences, or gunzip your file before feeding it to Ledger.


As a quick example, here's how I can produce a crude CSV file of the entries in my Expenses:Travel account:

	ledger -f myfile.gnucash --register-format "%D,%P,%A,%t\n" register Expenses:Travel

That format string is (roughly) "date,description,account,amount".

If you want to see what your GnuCash file would look like as a Ledger file you can do:

	ledger -f myfile.gnucash print

Now, one thing you should know if reading the Ledger documentation:  GnuCash files have an implicit 'Root Account', which Ledger is not expecting.   So if you try to run the default Ledger commands to get top-level reports, some of them won't work, because they will be expecting an 'Expenses' account, when it will actually be found under 'Root Account:Expenses'.   The 'balance' command will be remarkably quiet, for example.

Commands which are explicit about the account names will work fine, though.

The easiest way to strip out the root account is probably to convert your GnuCash file to Ledger text as shown above, do a global replace of 'Root Account:', and then feed the result into Ledger again to get the report you actually want.  You can do this all in one command line/script.

Here's an example

	ledger -f myfile.gnucash print \
		| sed -e 's/Root Account://g' \
		| ledger -f - -s balance

Hope that's useful to someone!

Quentin