R help - Dec 2007 - RJDBC to OpenOffice Calc as RODBC to MS Excel

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

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,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595