A small example. regards Christian
> library(gdata) > fname <- list.files("C:/dm/test",pattern=".xls", full.names = TRUE, recursive > =TRUE, ignore.case = TRUE) > > for (sp in 1:length(fname)) { + print(fname[sp]) + data <- read.xls(fname[sp], sheet=1, verbose=FALSE,perl="perl") + print(data) + } [1] "C:/dm/test/xls1/file1.xls" Converting xls file to csv file... Done. Reading csv file... Done. A B 1 100 100 2 200 200 [1] "C:/dm/test/xls1/file2.xls" Converting xls file to csv file... Done. Reading csv file... Done. A B 1 100 100 2 200 300 [1] "C:/dm/test/xls2/file5.xls" Converting xls file to csv file... Done. Reading csv file... Done. A B 1 100 100 2 200 300 3 200 100 regards Christian > -----Ursprüngliche Nachricht----- > Von: "Rolf Turner" <r.tur...@auckland.ac.nz> > Gesendet: 18.11.09 21:38:12 > An: "Mark W.Miller" <mark_wayne_mil...@yahoo.com> > CC: "r-help@r-project.org" <r-help@r-project.org> > Betreff: Re: [R] Re ading multiple Excel 2007 files with a loop > > Have you looked at the read.xls() function from the gdata package? > It automates the conversion to *.csv for you. It has worked seamlessly > for me on the occasions on which I've needed to use it. > > cheers, > > Rolf Turner > > On 19/11/2009, at 9:09 AM, Mark W. Miller wrote: > > > > > > > I have several hundred Excel 2007 data files in a folder. I would > > like to > > read every file in a single given folder using a loop. > > > > I have searched the FAQ, the forum archives here, other or older R > > boards > > and the R Import / Export documentation, and have asked some very > > knowledgeable R users without learning of a solution. I hope > > someone here > > can help. > > > > I understand that the most common suggestion is to convert the > > files to csv > > format. However, there are so many files in my case (ultimately > > > 1000) I > > would rather avoid doing that. > > > > I have also found many solutions to this problem for txt files and > > files in > > additional formats other than Excel 2007. > > > > I can read three Excel 2007 files one at a time with the following > > example > > code using R 2.10.0 on a computer running Windows (XP, I think): > > > > > > > > > > library(RODBC) > > > > > > channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, > > *.xlsx, > > *.xlsm, *.xlsb); > > DBQ=U:\\test folder\\testA.xlsx; ReadOnly=False") > > > > sqlTables(channel) > > > > my.data.A <- sqlFetch(channel, "Sheet1") > > > > odbcClose(channel) > > > > > > > > channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, > > *.xlsx, > > *.xlsm, *.xlsb); > > DBQ=U:\\test folder\\testB.xlsx; ReadOnly=False") > > > > sqlTables(channel) > > > > my.data.B <- sqlFetch(channel, "Sheet1") > > > > odbcClose(channel) > > > > > > > > channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, > > *.xlsx, > > *.xlsm, *.xlsb); > > DBQ=U:\\test folder\\testC.xlsx; ReadOnly=False") > > > > sqlTables(channel) > > > > my.data.C <- sqlFetch(channel, "Sheet1") > > > > odbcClose(channel) > > > > > > > > > > > > # However, when I attempt to read the same three files with the > > loop below I > > receive an error: > > > > > > > > > > library(RODBC) > > > > > > setwd("U:/test folder") > > > > > > fname <- list.files(pattern=".\\.xlsx", full.names = FALSE, > > recursive = > > TRUE, ignore.case = TRUE) > > > > z <- length(fname) > > > > print(z) > > > > > > for (sp in 1:z) { > > > > channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, > > *.xlsx, > > *.xlsm, *.xlsb); > > > > DBQ=U:\\test folder\\fname[sp]; ReadOnly=False") > > > > sqlTables(channel) > > > > my.data <- sqlFetch(channel, "Sheet1") > > > > print(my.data) > > > > odbcClose(channel) > > } > > > > > > > > > > # The error I receive states: > > > > Error in odbcTableExists(channel, sqtable) : > > Sheet1: table not found on channel > > > > > > # Thank you sincerely in advance for any help with this problem. > > > > Mark Miller > > > > Gainesville, Florida > ###################################################################### > Attention: > This e-mail message is privileged and confidential. If you are not the > intended recipient please delete the message and notify the sender. > Any views or opinions presented are solely those of the author. > > This e-mail has been scanned and cleared by MailMarshal > www.marshalsoftware.com > ###################################################################### > > ______________________________________________ > R-help@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@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.