So, you’d like to export one of your GnuCash accounts for manipulation in a spreadsheet, because you’ve put off learning how to create custom reports for GnuCash?
Ouch.
It is possible, but you will need to run an XSLT transform against your GnuCash data file, which will produce a Gnumeric compatible spreedsheet. From there, you can export to CSV or whatever else Gnumeric supports, if you need to. I exported to CSV since I do my spreadsheet work in OpenOffice.org’s Calc.
Fortunately, on Debian Sarge, you can install all the necessary software to arrive at a useful export of your GnuCash data with the installation of an insane amount of additional software.
# apt-get install gnumeric xalan
Once that’s complete, you’ll have both gnumeric and xalan, a Java XSLT processor, installed.
As mentioned in the WiKi FAQ for GnuCash, you will want to download a copy of the XSLT from xls factory and save it some place permanent, as you’ll probably want it later.
Now, make a copy of your GnuCash XML data file.
$ cp ~/doc/gnucash/accounts /tmp/accounts.edit $ vi /tmp/accounts.edit
As the aforementioned Web site explains, you must replace the initial instance of <gnc-v2> with a lengthy xmlns definition. (Replace the [ and ] with the usual greater than and less than signs respectively. Thanks WordPress.)
[gnc-v2 xmlns:gnc="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/io-gncxml-version-2.dtd#gnc" xmlns:book="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/io-gncxml-version-2.dtd#book" xmlns:cd="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/io-gncxml-version-2.dtd#cd" xmlns:act="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/account-v2.dtdt#act" xmlns:trn="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/transactions-v2.dtd#trn" xmlns:split="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/transactions-v2.dtdt#split" xmlns:cmdty="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/comodity-v2.dtd#cmdty" xmlns:ts="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/types.dtd#ts" xmlns:slots="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/types.dtd#slots" xmlns:slot="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/types.dtd#slot" xmlns:price="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/prices-v1.dtd#price" xmlns:pricedb="http://www.gnucash.org/lxr/gnucash/source/src/doc/xml/prices-v1.dtd#pricedb"]
Finally, you can run the XSLT against your modified GnuCash data file.
$ xalan -IN /tmp/accounts -XSL /tmp/gnucash2gnumeric_0.6.xsl -OUT /tmp/gnumeric.out
If you are using any scheduled transactions (SX) in GnuCash, the XSLT will fail. Fortunately, you can manually remove the offending tags at the end of your GnuCash data file and rerun the XSLT for the win!
XSLT Warning: Fatal Error.Occurred at file /tmp/accounts , line 50443 , column {null}.
The prefix 'sx' has not been mapped to any URI. (Line -1, column -1.)
SAXParseException Message is:
The prefix 'sx' has not been mapped to any URI (/tmp/accounts, line 50443, column 22)
Here are the offending SX entries: (Again, the greater and less signs have been eaten by WordPress.)
[gnc:schedxaction version="1.0.0"] ... [/gnc:schedxaction]
Remove those tags and everything in between and the XSLT should proceed without incident.
Once the process is completely, you can open the spreadsheet in gnumeric and work with it there or export it to CSV and work with it via some other application or programming language.
Update, October 23rd. Jody Goldberg emailed me the following tip:
Once it is in gnumeric format you can use the
ssconverttool from the command line to map it onto any of the export formats gnumeric supports.
I gave it a shot and found it works quite well.
jasonb@faith:~/tmp$ ssconvert --export-type=Gnumeric_Excel:excel_dsf testgnumeric.out foo.xls Reading file:///home/jasonb/tmp/testgnumeric.out Writing file:///home/jasonb/tmp/foo.xls
Ronald said,
February 19, 2007 at 9:26 am
Hi Jason,
Many thanks for posting these instructions online, they have been of great help to me.
One thing to note is that I had quite a few more errors similar to the one you list above:
“The prefix ’sx’ has not been mapped to any URI (/tmp/accounts, line 50443, column 22)”
but instead of finding the block of lines and deleting them I just added a line starting with “xmlns:sx”, similar to the existing ones, to the header. Not that I know what I’m doing, but the errors went away and the result looks ok.
As I don’t know what I’m doing with regards to xalan, and java in general, either, I also found that your XSLT conversion command did not work for me. Some googling revealed that what I had to enter to get the desired effect is
“java org.apache.xalan.xslt.Process -IN /tmp/accounts.edit -XSL gnucash2gnumeric_0.6.xsl -OUT /tmp/gnumeric.out”
(I’m sure you mean to use /tmp/accounts.edit as the input file - a typo?)
The conversion of the output file to Excel format worked well, however just for the fun of it I opened the original version in gnumeric and found it much easier to adjust the column widths there as gnumeric does the right thing when you select the whole table and double-click any column border - much more tedious in OpenOffice, and probably in Excel.
Thanks again, I hope my 3 Cents can help anyone, too.
Cheers,
Ronald
Ed said,
July 29, 2007 at 8:11 am
Brilliant posting - helped me import my GnuCash records into a new cash program in no time at all.
Many thanks for providing this succinct and accurate information!
phileas said,
October 22, 2007 at 12:48 pm
everything works fine, but when trying to open or convert the gnumeric file, i get the followin error: File has inconsistent SheetNameIndex element
any ideas?
thank you
Knut said,
December 22, 2007 at 8:02 am
I had the same problem as phileas and had to edit the input-file for gnumeric (/tmp/gnumeric.out in Jason’s example) to overcome it.
Problem was: The sheet names looked like this:
ACCOUNTS
DIARY
1__Root Account
2__Aktiva
3__Sachanlagen
…….
102__Anfangsbestand
103__Ausbuchungskonto-EUR
For every entry in the sheet name index there is an account later on in gnumeric.out that looks like this:
103__Ausbuchungskonto-EUR
……
BUT: After my last legitimate account 103__Ausbuchungskonto-EUR there were generated a number of additional (empty) accounts with names like “104__” up to 143__b24474cd40802651f696a13201fc806b.
I deleted these i.e. deleted everything between and and could import into gnumeric.
Without warranty, of course
Knut
Knut said,
December 22, 2007 at 8:14 am
I am sorry, but my comment was multilated because it seems that the greater than and less than signs are interpreted by the server of this site as Jason mentioned before. Anyhow there was a discrepancy between the Sheet Names as enumerated between [gmr:SheetNameIndex] and [/gmr:SheetNameIndex] and the corresponding “accounts” later on between [gmr:Sheet…] and [/gmr:Sheet]. This had to be corrected by deleting the accounts that had no entry in the Sheet name Index.
Please post if you need further clarifications.
matias said,
February 7, 2008 at 7:12 pm
I can’t believa that exporting a simple, simple, and stupid CSV file is SO!! difficult!! Gnucash has a terrible interfase.