Re: [R] RJDBC to OpenOffice Calc as RODBC to MS Excel

From: Marc Schwartz <>
Date: Fri, 14 Dec 2007 07:28:23 -0600

On Fri, 2007-12-14 at 07:31 +0000, Prof Brian Ripley wrote:
> On Fri, 14 Dec 2007, Metz, Thomas (IRRI) wrote:
> > Under Windows, I have used RODBC to connect to Excel spreadsheets as per
> > the example below:
> >
> > library(RODBC);
> > connect = odbcConnectExcel("testdata.xls");
> > query = "SELECT [data$.ethn], [data$.sex], [data$.age],
> > [data$.height], [data$.weight],
> > [label$.label]
> > FROM [data$], [label$]
> > WHERE [data$.ethn] = [label$.ethn];"
> > data = sqlQuery(connect, query);
> > odbcClose(connect);
> >
> > [data$] and [label$] are two named sheets in the Excel spreadsheet
> > testdata.xls. [.ethn], [.sex], [.age], [.height], [.weight], and
> > [.label] are cloumn names that appear in the first row in the sheets. I
> > can also have UNION queries that allow me to overcome the spreadsheet
> > row limitation of a single sheet. The idea is to allow normalization of
> > data in a spreadsheet and leveraging the power of SQL, without using a
> > database.
> >
> > Can the same be done under Windows (Linux?) with OpenOffice Calc using
> > RJDBC? Are there ODBC drivers for OpenOffice Calc?
> An awful lot of that is Microsoft warts on SQL, so it will not be
> portable. But in a more standard syntax (drop the [] and $) it should be
> doable over any connection that supports SQL queries.
> The question is whether OO calc has suitable drivers as an ODBC/JDBC
> server. Not a question for this list! (I suspect the answer is no:
> Microsoft's drivers effectively use the Access engine to work with
> spreadsheet files and even plain text. I don't even see drivers for OO
> base.)

There are none to my knowledge. Most of's ODBC/JDBC integration is one-way. That is, it can connect within the suite and to external sources, but does not appear to provide connectivity to enable external applications to acquire data stored within's apps.

Base is an embedded version of HSQLDB (, which is a java based application. In theory, it would support a JDBC interface, but I have seen none and when the subject comes up on the lists, no solutions are forthcoming. It's too bad, at least within this context, that the folks elected to use HSQLDB rather than SQLite, which was the the other option under consideration. Perhaps Sun's influence, vis-a-vis Java, won the day here.

Bearing in mind that's Write and Calc documents are just 'zipped' XML files, it would be possible to parse the data stored within such documents. I suspect Max Kuhn has spent much time on this for odfWeave.

There are Perl modules that can provide a level of interaction here. For example, OpenOffice::Parse::SXC
( provides the means to parse a Calc file directly, without needing the API.

One could wrap that module in an R function via a system() call and then interact with a Calc file directly.

HTH, Marc Schwartz mailing list PLEASE do read the posting guide and provide commented, minimal, self-contained, reproducible code. Received on Sat 15 Dec 2007 - 03:15:54 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Sat 15 Dec 2007 - 04:30:19 GMT.

Mailing list information is available at Please read the posting guide before posting to the list.