We are going to do two things in this tutorial. First we are going to export the GnuCash data into a SQLite database. SQLite is a popular open source database that is being used behind the scenes in many well-known applications. It is possible that you already use SQLite, though you may not even be aware of that. As a prerequisite to the next step, get an ODBC driver for SQLite3 (http://www.ch-werner.de/sqliteodbc/) or a similar one and install it on your PC. This ODBC driver enables client applications such as OpenOffice.org Base to connect to databases such as SQLite3 as long as both support the ODBC (Open Database Connectivity) standard. After exporting the GnuCash data into SQLite3 database, we are going to use OpenOffice.org Base1 to connect to that exported database and run a query to extract specific data of interest to us.
- 1. Select from the menu File | Save As. The Save As dialog will open. Click on the Data Format dropdown and select sqlite3 as shown in the following screenshot:
- 2. Click the Save As button. Select the folder where you want to save this and give it the file name MACS-SQLite. It will be saved as
MACS-SQLite.gnucash. - 3. Prerequisites: In order to connect to this SQLite3 database from OpenOffice.org and other applications, you need an ODBC driver. As mentioned earlier, evaluate, select, download, and install an ODBC driver for SQLite3.
- 4. Once the ODBC driver is installed, you are ready to set up a DSN (Data Source Name), for this database so that you can connect to it from OpenOffice.org and other such applications. In Windows XP select Control Panel | Administrative Tools | Data Sources (ODBC). Windows Vista and 7 should be similar.
- 5. The ODBC Data Source Administrator dialog will open. In the System DSN tab, under System Data Sources select SQLite3 Data Source click Add and the Create New Data Source dialog will open. Select SQLite3 ODBC Driver and click Finish.
- 6. The SQLIte3 ODBC DSN Configuration dialog will open. Enter Data Source Name as MACS and for Database Name click Browse and find the MACS-SQLite.gnucash file that you saved from GnuCash. Click OK. You should see a new System DSN created with the name MACS as shown in the following screenshot:
- 7. Prerequisites: Make sure you have OpenOffice.org installed in your system. This tutorial, as well as the screenshots, is based on the OpenOffice.org version 3.2 running on Windows XP. If you have a different version or are on another operating system, please look for equivalent steps.
- 8. We are planning to use OpenOffice.org Base, which is the database application. Launch OpenOffice.org and under Create a new document click on the Database icon. You can also select from the menu File | New | Database. The Database Wizard will open, as shown in the following screenshot:
- 9. Select the Connect to an existing database option, select ODBC in the drop-down list, and click Next >>.
- 10. The second step in the database wizard is to Set up a connection to an ODBC database. Click on the Browse button in the Name of the ODBC data source on your system field. The Data Source dialog will open. Now that you have already set up an ODBC DSN, you should see MACS in that list, as shown in the next screenshot. Select MACS.
- 11. Click OK to launch OpenOffice.org Base.
- 12. Click Queries on the left pane. Click Create Query in design view. The Add Table or Query dialog will open. Select accounts and click Add. Similarly add splits and transactions tables.
- 13. Select from the menu Insert | New Relation. The Join Properties dialog will open. Make an inner join from the accounts table guid field to the splits table account_guid field. In the same way, create another inner join from the splits table tx_guid field to the transactions table guid field.
- 14. Drag the name field from the accounts table and drop it into the Field list below. Similarly add description and post_date from the transaction table as well as value_num from the splits table.
- 15. We want only transactions from the Checking Account. In the Criterion line under name, type Checking Account and tab out. OO Base will add single quotes.
- 16. We want them ordered by amount, with the largest amount first. In the Sort line under value_num, select descending from the drop-down list.
- 17. Now click the Run Query button to see the results of the query duly filtered and sorted, as shown in the following screenshot:
- 18. Don't forget to save the query for future use.
"OK, I was able to export the GnuCash data into a SQL database, connect to it from OpenOffice.org, and run the query successfully. Great! But when I look at the result, I don't seem to have achieved anything useful. Tell me again, why am I jumping through all these hoops?" Well, that was just a means to an end, not the end in itself. No wonder you were underwhelmed. In the next couple of sections, we will look at creating custom reports and charts as well as application integration. All of those will require very specific data. These queries will supply that specific data to them.
In the preceding tutorial, we looked at a small sample of data from three of the tables. We were able to get the specific data we wanted, filter out unwanted data, and order the data the way we wanted it. There are a total of about two dozen tables and you can do plenty with those, depending on what business problems you are trying to solve.
In addition to SQLite3, GnuCash Export to SQL database gives you the option to export to the following popular open source databases as well:
SQLite3 has a small footprint and does not need a lot of memory, disk space, or processing resources. On the other hand, if you are looking for a more robust and heavy duty database, you might want to consider MySQL or PostgreSQL. Depending on your business need and familiarity, you might want to choose one of these options for your custom reporting, query, and application integration needs.
- When you simply click Save, GnuCash saves in its default data format. What is GnuCash's default data format?
a.
xmlb.
gnucashc.
sqlite3d.
csv