-----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:
Thank you both.
Gabor, do I need perl to be installed onj my computer to use those
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
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
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
cat("Caution: Be careful with mixed-type columns that begin
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>
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