[R] strange behavior of RODBC and/or ssconvert

[stefan.duke at gmail.com]

Dear All,

I have the following problem when reading files (a lot of them) in the
spreadsheetML format into R. The spreadsheetML format is an xml format
to allow easy import of multisheet data in Excel. As far as I can see,
a direct import into R (using the XML package) is not feasible. I use
the software ssconvert (included in Gnumeric) and call it from R. It
converts the spreadsheetML into xls format.
When I now import the newly created xls-file using RODBC package, the
last row in each sheet is missing. However, when I open the xls-file
the last row is present (hence, ssconvert doesn't delete it). When I
now save the xls-file, and import it again using the RODB package, the
data is now complete.

Any idea what to do about that? My main problem is to get the
spreadsheetML into R so I tried other file formats to which ssconvert
can convert to, but only excel supports multisheets.


Best,
Stefan


Example code:
system(paste('ssconvert "excelcohortdata_men_reference scenario.xml"
"excelcohortdata_men_reference scenario22.xls"'))
channel1<-odbcConnectExcel("excelcohortdata_men_reference scenario10.xls")
odbcGetInfo(channel1)
sqlTables(channel1)
sqlQuery(channel1, "select * from \"age 9in 2010$\"" )
sqlFetch(chanel1, "age 9in 2010""