Tech Recipe: Loading SQL Data into Gnumeric


[libgda Linux database setup dialog]Loading SQL data directly into the Gnumeric spreadsheet is easy though (as of Gnumeric 1.6) rather undocumented.  Here's the recipe:

1) Set up a database connection using the "gnome-­database-­properties" tool from package "libgda".   If a "provider" is not available for your particular database, check for a package with your distribution.  On SUSE Linux 10.0, the package for Mysql is called "libgda-mysql".  Note that the database could be local to your machine, or hosted on a remote machine -- just point the provider appropriately.

2) Open Gnumeric, and select a range of cells.

3) Enter a formula of the format:

=execSQL("connection_name", "username", "password", "SQL Statement")

4) Don't press enter, but rather use the rather obscure 3-key syntax inherited from Microsoft Excel: CTRL-SHIFT-ENTER.  You may have to press this twice, and you may have to select the database password twice.

What you've created is an array formula.  Your entered formula will magically change to read something like this real example:

={execSQL("salesDB", "fred", "fish", "SELECT addr1,addr1 FROM sp_addr")}(21,3)[0][0]

Where the {braces} flag an array formula, the (round brackets) define the array extent, and [square brackets] show the location in the array.  To edit this formula, don't press ENTER, press CTRL-SHIFT-ENTER (otherwise Gnumeric will give you the error message 'would split an array').

Note that you must anticipate in advance how many rows and columns your database query will return.  And it appears to be impossible to get Gnumeric to automatically insert the column names into the spreadsheet.  Hopefully future versions of Gnumeric will use a less obscure syntax for dealing with array formulas.

Additional Resources:

Last update: March 2006 for Gnumeric 1.6.  Page Keywords: SQL, Postgres, Mysql, Oracle, open source, ODBC, database, execSQL, DB, Excel compatible Gnumeric spreadsheet workbook, SUSE Linux 10.0, RedHat Linux, OpenOffice.