GnuCash export to gnumeric and CSV

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 ssconvert tool 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

14 Comments

  1. Ronald
    Posted 2/19/2007 at 9:26 am | Permalink

    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

  2. Ed
    Posted 7/29/2007 at 8:11 am | Permalink

    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!

  3. phileas
    Posted 10/22/2007 at 12:48 pm | Permalink

    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

  4. Knut
    Posted 12/22/2007 at 8:02 am | Permalink

    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

  5. Knut
    Posted 12/22/2007 at 8:14 am | Permalink

    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.

  6. matias
    Posted 2/7/2008 at 7:12 pm | Permalink

    I can’t believa that exporting a simple, simple, and stupid CSV file is SO!! difficult!! Gnucash has a terrible interfase.

  7. James Van Bokkelen
    Posted 6/7/2008 at 12:27 pm | Permalink

    I tried this with GnuCash 2.2.0 on FreeBSD 7.0. After installing the package ‘xalan-c’ and identifying changes in the command line, I didn’t get anywhere. The first big difference is that the 2.0 file has its own version of the XML header that the XSL factory said needed to be added. The second is that the XSL factory’s Gnucash2Gnumeric.xsl v0.6 appears not to find tokens it’s looking for and makes Xalan exit with a message before parsing any of the input file. So this is on hold till I have time to learn another programming language.

  8. Posted 10/22/2008 at 12:30 am | Permalink

    If you don’t mind the competition:
    I have been writing an OpenOffice.org macro that will read GnuCash output data and import it into OpenOffice.org’s embedded database, so that thereafter you can evaluate them e.g. in Ooo-Calc. I wrote this at first for myself only, but now I have made it more presentable and fit for general application.
    Downloading instructions and a detailed step-by-step description on its use can be found on my website http://www.alice-dsl.net/gnuc2ooo/gnuc2ooo_en/intro.
    Kind regards
    Knut

  9. Posted 6/8/2009 at 1:38 am | Permalink

    I’ve made some mods to the .xsl file to get it working for Gnucash 2.2.6

    Feel free to try if it works for you.

    I also found that the xalan switches were no longer valid in uppercase, so this is the command line I use -

    xalan -in test01 -xsl ./gnucash2gnumeric_0.7.xsl -out test01

    Cheers,
    Andrew Rose

  10. Posted 6/14/2009 at 5:26 am | Permalink

    http://sites.google.com/site/gnucashexport/

  11. Lorenzo Lütjens
    Posted 10/15/2009 at 10:09 am | Permalink

    Thank you Andrew for the nice work!
    Cograts,
    Lorenzo Lutjens

  12. Greg
    Posted 1/28/2010 at 7:32 pm | Permalink

    Here’s how I got all this to work with GNUCash 2.2.9, in Fedora:

    1) cd where_gnucash_file_is
    2) mkdir tmp; cd tmp
    3) sudo yum install -y gnumeric libxslt wget
    4) hash -r # if in bash, to update the path cache
    5) wget http://xslfactory.free.fr/gnucash2gnumeric_0.6.xsl
    6) cp ../my_gnucash_file my_gnucash_file.gz
    7) gunzip my_gnucash_file.gz
    8) edit gnc-v2 tag in my_gnucash_file as shown in this article.
    9) xsltproc -o my_gnucash_file.gnumeric gnucash2gnumeric_0.6.xsl my_gnucash_file
    10) inspect the SheetNameIndex entries versus the Sheets with these greps:
    11) grep “SheetName” my_gnucash_file.gnumeric. Note the last sheet.
    12) grep “gmr:Name>” my_gnucash_file.gnumeric. Note if there are more sheets than found in the above step.
    13) edit the bogus sheets out of my_gnucash_file.gnumeric (search for the last valid gmr:Name, delete from the next gmr:Name to /gmr:Sheets)
    14) gnumeric my_gnucash_file.gnumeric

  13. Shadok
    Posted 2/25/2010 at 6:34 pm | Permalink

    Thanks Greg, this works !

  14. Alvaro
    Posted 10/22/2010 at 5:00 pm | Permalink

    +1 — thanks Greg, ’cause your recipe worked perfectly for me without a hitch, and thanks Jason for posting the original article!

Post a Comment

Your email is never shared. Required fields are marked *

*
*