stefan.duke at gmail.com
2010-May-04 14:49 UTC
[R] strange behavior of RODBC and/or ssconvert
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""
The original seems not to have gotten through. Here it is again. On Tue, May 4, 2010 at 11:14 AM, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:> Try a few of the solutions here: > http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows > and see if they all give you the same result. >
Possibly Parallel Threads
- Excel files-suggested manual addition
- R-help Digest, Vol 87, Issue 5
- TypeError: no implicit conversion of Symbol into Hash when submitting form to upload files
- Read in from multiple Excel wksheets
- Reading a SpreadsheetML / XMLNS into R xmlns="urn:schemas-microsoft-com:office:spreadsheet