I'd like to be able to read multiple sheets from an excel workbook and use the sheet name to name the resulting dataframe using RODBC. at the moment i've figured out how to do it the long way (see below) but feel sure that there is a speedier & possibly automatic way to do it in R. i've tried to run a loop using sqlTables but it seemed to break the connection. unless i've missed something, i cant see a solution to this on the help list. grateful for any help or pointers simeon # long way library(RODBC) filepath <- "C:/Data/workbook.xlsx" connect <- odbcConnectExcel2007(filepath) tbls <- sqlTables(connect) sheet1 <-sqlFetch(channel=connect,sqtable='sheet1') sheet2 <-sqlFetch(channel=connect,sqtable='sheet2') sheet3 <-sqlFetch(channel=connect,sqtable='sheet3') .. etc close(connect) [[alternative HTML version deleted]]
simeon duckworth wrote:> > I'd like to be able to read multiple sheets from an excel workbook and use > the sheet name to name the resulting dataframe using RODBC. >In Microsoft theory, something like the below should be ok (note the $), but never managed to get this to work. The same method works perfectly when you have name ranges within a spreadsheet. If you only have the sheet names, you should use package xlsReadWrite which is rather fast, but has some limitations in the non-commercial version. Dieter library(RODBC) filepath <- "workbook.xlsx" tabls = list() channel <- odbcConnectExcel2007(filepath) for (i in 1:2) tabls[[i]] = sqlQuery(channel, paste("SELECT * from [Tabelle",i,"$]",sep="")) odbcClose(channel) # I prefer an explicit odbcClose, but generic close.RODBC should work. -- View this message in context: http://www.nabble.com/Read---name-multiple-excel-sheets-using-RODBC-tp23760020p23760912.html Sent from the R help mailing list archive at Nabble.com.
2009/5/28 Dieter Menne <dieter.menne at menne-biomed.de>:> If you only have the sheet names, you should use package xlsReadWrite which > is rather fast, but has some limitations in the non-commercial version.what limitations, i.e. features do you miss? Cheers, Hans-Peter
Hans-Peter Suter wrote:> >> If you only have the sheet names, you should use package xlsReadWrite >> which >> is rather fast, but has some limitations in the non-commercial version. > > what limitations, i.e. features do you miss? > >Reading of named ranges. Dieter -- View this message in context: http://www.nabble.com/Read---name-multiple-excel-sheets-using-RODBC-tp23760020p23761745.html Sent from the R help mailing list archive at Nabble.com.
Using the package rcom (by Thomas Baier) you can do everything you want since you have the full power of COM at your disposal. Dieter Menne wrote:> > > Hans-Peter Suter wrote: >>> If you only have the sheet names, you should use package xlsReadWrite >>> which >>> is rather fast, but has some limitations in the non-commercial version. >> what limitations, i.e. features do you miss? >> >> > > Reading of named ranges. > > Dieter >-- Erich Neuwirth, University of Vienna Faculty of Computer Science Computer Supported Didactics Working Group Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-39459