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

[Rolf Turner]

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
######################################################################