[R] number of Excel worksheets

[William Dunlap]

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-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf
> Of Santosh
> Sent: Monday, May 07, 2012 5:54 PM
> To: r-help at 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.stat at 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
> > <ggrothendieck at 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 <pozsgaig at 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.stat at 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 <pozsgaig at 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 at 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 at 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 at 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 at 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]]