[R] number of Excel worksheets

[Steve_Friedman at nps.gov]

Kevin

This function is very valuable to me as well. Can you explain what the
function is looking for with regards to the arguments sheet and condition?

Thanks
Steve

Steve Friedman Ph. D.
Spatial Statistical Analyst
Everglades and Dry Tortugas National Park
950 N Krome Ave (3rd Floor)
Homestead, Florida 33034

Steve_Friedman at nps.gov
Office (305) 224 - 4282
Fax     (305) 224 - 4147


                                                                           
             Kevin Wright                                                  
             <kw.stat at gmail.co                                             
             m>                                                         To 
             Sent by:                  Gábor Pozsgai <pozsgaig at gmail.com>  
             r-help-bounces at r-                                          cc 
             project.org               r-help at r-project.org                
                                                                   Subject 
                                       Re: [R] number of Excel worksheets  
             02/05/2010 09:41                                              
             AM                                                            
                                                                           
                                                                           
                                                                           
                                                                           




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

             [[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.