[R] Re ading multiple Excel 2007 files with a loop

[Mark W. Miller]

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.