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
--
View this message in context:
http://old.nabble.com/Reading-multiple-Excel-2007-files-with-a-loop-tp26414828p26414828.html
Sent from the R help mailing list archive at Nabble.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.
