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

From: Prof Brian Ripley <>
Date: Fri, 14 Dec 2007 07:31:09 +0000 (GMT)

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.)

> I know that the right solution would be to use a database, but this is
> outside the comfort zone of many users who rely mainly on spreadsheets
> to collect, manipulate and analyze their data.
> Thomas Metz
> International Rice Research Institute
> Philippines

Brian D. Ripley,        
Professor of Applied Statistics,
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

______________________________________________ mailing list
PLEASE do read the posting guide
and provide commented, minimal, self-contained, reproducible code.
Received on Fri 14 Dec 2007 - 07:39:30 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 - 03:30:19 GMT.

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