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

[Metz, Thomas (IRRI)]

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? 

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