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

Metz, Thomas (IRRI) T.METZ at CGIAR.ORG
Fri Dec 14 04:45:06 CET 2007

Under Windows, I have used RODBC to connect to Excel spreadsheets as per
the example below: 

connect = odbcConnectExcel("testdata.xls");
query = "SELECT [data$.ethn], [data$.sex], [data$.age], 
                [data$.height], [data$.weight], 
         FROM [data$], [label$] 
         WHERE [data$.ethn] = [label$.ethn];"
data = sqlQuery(connect, query);

[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

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

More information about the R-help mailing list