contentsData() will report the names of the "sheets" in an Excel file, or the names of the datasets in a SAS transport file, or the names of the tables in a datbase. importData has a pageNumber argument to let you import from a specific Excel sheet.
Bill Dunlap Spotfire, TIBCO Software wdunlap tibco.com > -----Original Message----- > From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On > Behalf > Of Santosh > Sent: Monday, May 07, 2012 5:54 PM > To: r-help@r-project.org > Subject: Re: [R] number of Excel worksheets > > Hello experts!! > I apologize for posting SPlus related query here..badly in need of relevant > info.. > > I usually use R (and your advice/tips) for my daily work. Was wondering if > there is an equivalent of "sheetCount" of the package "gdata" avaailable in > Splus 8.2? I would like to get the total number of "tabs" (aka pages) in an > MS Excel workbook. > > Thanks, > Santosh > > On Fri, Feb 5, 2010 at 6:24 PM, Kevin Wright <kw.s...@gmail.com> wrote: > > > Sorry, I thought the code was clear, but probably not. As far as I know, > > the "gdata" package requires perl. My read.xls function requires RODBC. > > If > > you don't have perl, my function works well, but if you have perl, the > > gdata > > package can do a better job of reading mixed-type (character/numeric) > > columns from Excel. The arguments to my read.xls function: > > file: name of the Excel file > > sheet: this is the name that appears on the tab at the bottom of the > > worksheet > > condition: this is an additional SQL expression that can be passed to the > > RODBC calls. > > > > Kevin > > > > > > On Fri, Feb 5, 2010 at 9:27 AM, Gabor Grothendieck > > <ggrothendi...@gmail.com>wrote: > > > > > Yes. Get it from here: > > > http://www.activestate.com/activeperl/ > > > > > > On Fri, Feb 5, 2010 at 10:10 AM, Gábor Pozsgai <pozsg...@gmail.com> > > wrote: > > > > Thank you both. > > > > Gabor, do I need perl to be installed onj my computer to use those > > > functions? > > > > > > > > Gabor > > > > > > > > 2010/2/5 Kevin Wright <kw.s...@gmail.com>: > > > >> I have a function to read xls files that tells me the name of the > > > available > > > >> sheets. See below. > > > >> > > > >> Kevin Wright > > > >> > > > >> > > > >> read.xls = function (file, sheet, condition) > > > >> { > > > >> if (missing(file)) > > > >> stop("No file specified.") > > > >> if (!file.exists(file)) > > > >> stop("File ", file, " does not exist in directory ", > > > >> getwd()) > > > >> if (missing(sheet)) > > > >> stop("No sheet specified.") > > > >> if (!require(RODBC, quietly = TRUE)) > > > >> stop("The RODBC package is required.") > > > >> channel = odbcConnectExcel(file) > > > >> if (!RODBC:::odbcValidChannel(channel)) > > > >> stop("first argument is not an open RODBC channel") > > > >> tables <- sqlTables(channel) > > > >> tables <- if (is.data.frame(tables)) > > > >> tables[, "TABLE_NAME"] > > > >> else "" > > > >> tables <- gsub("\\$$", "", gsub("'", "", tables)) > > > >> if (!(sheet %in% tables)) { > > > >> odbcClose(channel) > > > >> msg <- paste(paste("'", tables, "'", sep = ""), collapse = " > > ") > > > >> stop("Couldn't find requested sheet.\n", " Available sheets > > > are: ", > > > >> msg) > > > >> } > > > >> qsheet <- paste("[", sheet, "$]", sep = "") > > > >> if (missing(condition)) > > > >> data <- sqlQuery(channel, paste("select * from", qsheet)) > > > >> else data <- sqlQuery(channel, paste("select * from", qsheet, > > > >> condition)) > > > >> odbcClose(channel) > > > >> if (length(grep("#", names(data))) > 0) > > > >> cat("Caution: Column names may have had '.' changed to > > '#'.\n") > > > >> cat("Caution: Be careful with mixed-type columns that begin > > with\n") > > > >> cat(" some (15?) rows that are only numeric.\n") > > > >> cat(" Use str() and summary() to check the import.\n") > > > >> return(data) > > > >> } > > > >> > > > >> > > > >> On Fri, Feb 5, 2010 at 8:13 AM, Gábor Pozsgai <pozsg...@gmail.com> > > > wrote: > > > >>> > > > >>> Dear All, > > > >>> > > > >>> I would like to count or list the names of the existing worksheets > > > >>> within an .xls file. Any hints? > > > >>> > > > >>> Thaks, > > > >>> > > > >>> Gabor > > > >>> > > > >>> -- > > > >>> Pozsgai Gábor > > > >>> www.coleoptera.hu > > > >>> www.photogabor.com > > > >>> > > > >>> ______________________________________________ > > > >>> R-help@r-project.org mailing list > > > >>> https://stat.ethz.ch/mailman/listinfo/r-help > > > >>> PLEASE do read the posting guide > > > >>> http://www.R-project.org/posting-guide.html > > > >>> and provide commented, minimal, self-contained, reproducible code. > > > >> > > > >> > > > >> > > > >> -- > > > >> Kevin Wright > > > >> > > > >> > > > > > > > > > > > > > > > > -- > > > > Pozsgai Gábor > > > > www.coleoptera.hu > > > > www.photogabor.com > > > > > > > > ______________________________________________ > > > > R-help@r-project.org mailing list > > > > https://stat.ethz.ch/mailman/listinfo/r-help > > > > PLEASE do read the posting guide > > > http://www.R-project.org/posting-guide.html > > > > and provide commented, minimal, self-contained, reproducible code. > > > > > > > > > > ______________________________________________ > > > R-help@r-project.org mailing list > > > https://stat.ethz.ch/mailman/listinfo/r-help > > > PLEASE do read the posting guide > > > http://www.R-project.org/posting-guide.html > > > and provide commented, minimal, self-contained, reproducible code. > > > > > > > > > > > -- > > Kevin Wright > > > > [[alternative HTML version deleted]] > > > > > > ______________________________________________ > > R-help@r-project.org mailing list > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide > > http://www.R-project.org/posting-guide.html > > and provide commented, minimal, self-contained, reproducible code. > > > > > > [[alternative HTML version deleted]] ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
