Re ading multiple Excel 2007 files with a loop

library(gdata)
fname <- list.files("C:/dm/test",pattern=".xls", full.names = TRUE, recursive =TRUE, ignore.case = TRUE)

for (sp in 1:length(fname)) {

-----Urspr?ngliche Nachricht-----
Von: "Rolf Turner" <r.turner at auckland.ac.nz>
Gesendet: 18.11.09 21:38:12
An: "Mark W.Miller" <mark_wayne_miller at yahoo.com>
CC: "r-help at r-project.org" <r-help at 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
######################################################################