[R] Yet another question about importing Excel *.xls files

[Gabor Grothendieck]

Based on the information below C:\TestR is not a file.  Its a directory.

Try any of these three:

read.xls(file.choose(), sheet = 3, pattern = "NAPT ID", as.is = TRUE)

read.xls("C:\\TestR\\MAP_2009Round2_2002Soiltest9-21-09.xls", sheet =
3, pattern = "NAPT ID", as.is = TRUE)

setwd(""C:\\TestR")
read.xls("MAP_2009Round2_2002Soiltest9-21-09.xls", sheet = 3, pattern
= "NAPT ID", as.is = TRUE)

There is a development version of read.xls that can accept sheet names
as well as numbers but its not released.  If you prefer to use
read.xls over the alternatives due to the availability of the pattern
argument then install the RDCOMClient package and try this:

sheetNames <- function(xls = file.choose()) {
	require(RDCOMClient)
	xl <- COMCreate("Excel.Application")
	xl[["Visible"]] <- TRUE
	on.exit({ awb <- xl[["Activeworkbook"]]; awb[["Saved"]] <- TRUE; xl$Quit() })
	wb <- xl[["Workbooks"]]$Open(xls)
	ws <- wb[["Worksheets"]]
	n <- ws[["Count"]]
	ithSheetName <- function(i) ws$Item(i)[["Name"]]
	sapply(1:n, ithSheetName)
}

xls <- file.choose(); xls
snames <- sheetNames(xls); snames
snumber <- match("Paste Special", snames)
DF <- read.xls(xls, sheet = snumber, as.is = TRUE, pattern = "NAPD ID")


On Thu, Jan 21, 2010 at 8:44 AM, Jerry Floren <jerry.floren at state.mn.us> wrote:
>
> Hi Gabor,
>
> Perhaps I am missing something in my file names that I just can't see.
> Whenever I have had problems reading a file into R, it always has been
> because of some typo that I have made.
>
> ## This is what I am trying to run. The worksheet is the third one.
> Originally it was named "Paste Special", but I changed it to "Sheet3". The
> first cell (A1) on Sheet3 is named "NAPT_ID"
>
> library(gdata)
> DF <- read.xls("C:/TestR", pattern = "NAPT_ID", sheet=3, as.is = TRUE)
>
>
> This is the error message I get:
>
>> library(gdata)
>
> Attaching package: 'gdata'
>
>
>        The following object(s) are masked from package:utils :
>
>         object.size
>
>> DF <- read.xls("C:/TestR", pattern = "NAPT_ID", sheet=3, as.is = TRUE)
> Converting xls file to csv file... Error in system(cmd, intern = !verbose) :
> perl not found
> Error in file.exists(tfn) : invalid 'file' argument
>>
>
> ## Here is an example file name from "file.choose()" in this directory
>
>> file.choose()
> [1] "C:\\TestR\\MAP_2009Round2_2002Soiltest9-21-09.xls"
>> file.choose()
> [1] "C:\\TestR\\MAP_2009Round2_2006BestTest10-2-09.xls"
>> file.choose()
> [1] "C:\\TestR\\MAP_2009Round2_4006A&LWestern10-1-09.xls"
>>
> I ran it again after removing all the Excel files in this directory except
> the three listed above and had similar error messages:
>
>> library(gdata)
>> DF <- read.xls("C:/TestR", pattern = "NAPT_ID", sheet=3, as.is = TRUE)
> Converting xls file to csv file... Error in system(cmd, intern = !verbose) :
> perl not found
> Error in file.exists(tfn) : invalid 'file' argument
>>
>
> Next, I renamed the Excel files to simpler names and also removed all the
> worksheets except the one with the data I needed. I renamed that one Sheet1
> instead of "Paste Special". With these changes, I simplified the expression,
> but I am still getting the same error messages.
>
>> DF <- read.xls("C:/TestR", as.is = TRUE)
> Converting xls file to csv file... Error in system(cmd, intern = !verbose) :
> perl not found
> Error in file.exists(tfn) : invalid 'file' argument
>>
>
> Thanks,
>
> Jerry Floren
> Minnesota Department of Agriculture
> --
> View this message in context: http://n4.nabble.com/Yet-another-question-about-importing-Excel-xls-files-tp1018699p1049735.html
> Sent from the R help mailing list archive at Nabble.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.
>