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

[Jerry Floren]

Sorry Gabor, but I am not quite there yet. Thanks David for your suggestion
on xlsReadWrite. Your message was posted while I was composing this one.

The Excel worksheet I want to read in is named "Paste Special"

I started with this code and thought it worked:

### Start of Code ###

library(RODBC) 

setwd("C:/Documents and Settings/jfloren/My Documents/Test_R") 

fname <- list.files(pattern=".\\.xls", full.names = FALSE, recursive = TRUE,
ignore.case = TRUE) 

z <- length(fname) 

print(z) 

for (sp in 1:z) { 

channel <- odbcDriverConnect(paste("DRIVER=Microsoft Excel Driver (*.xls,
*.xlsx, *.xlsm, *.xlsb); DBQ= C:/Documents and Settings/jfloren/My
Documents/Test_R\\",fname[sp],"; ReadOnly=False",sep='')) 
  
sqlTables(channel) 

alldata <- sqlFetch(channel, "Paste Special") 

print(alldata) 

odbcClose(channel) 
}

### Tried to write the individual files into one large *.TXT file (tab
separated)

#### Set up data frame for All Data  ####

gdata = data.frame(alldata)

write.table(gdata, file = "C:/Documents and Settings/jfloren/My
Documents/R_Statistics/Manure/All_03_to_09/All_MAP_Results.txt", append =
TRUE, quote = TRUE, sep = "\t", eol = "\n", na = "NA", dec = ".", row.names
= FALSE, col.names = TRUE, qmethod = c("escape", "double"))

#### End of Code ###

When I set up my data frame, only the last .XLS file name contained in the
folder was in the ALL_MAP_Results.txt file. Although I could see all the lab
results listed when I scrolled up in the R window, I think each lab was
overwriting without appending on the "alldata." There were only 61
observations when I typed str(alldata).

Next, I tried Gabor's code, which is so much more compact:

### Start of Gabor's code ###
library(gdata) 
DF <- read.xls(U, pattern = "SAMPLE", as.is = TRUE) 
##  End of Gabor's code ##

Is "U" the directory and path? Is "SAMPLE" the .XLS worksheet name (Paste
Special) that I want R to read?

## The following are the error messages: ##

> library(gdata) 

Attaching package: 'gdata'


        The following object(s) are masked from package:utils :

         object.size 

> DF <- read.xls("C:/Documents and Settings/jfloren/My Documents/Test_R",
> pattern = "Paste Special", 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
> 
> ls()
character(0)
> 

## End of error messages ##

Thanks for all of your help. Time to go home for the night.

Thanks,

Jerry Floren
Minnesota Department of Agriculture



-- 
View this message in context: http://n4.nabble.com/Yet-another-question-about-importing-Excel-xls-files-tp1018699p1020039.html
Sent from the R help mailing list archive at Nabble.com.