I gather from reading the back-issues of r-help that it should be possible (modulo a number of caveats) to read an excel (yuck!) file into R using RODBC. I have obtained and installed ODBC and the RODBC package, but cannot for the life of me figure out how to go about it. Can anyone give me a simple recipe? I have an excel file on cdrom, say: /mnt/cdrom/melvin.xls I have started R and loaded the RODBC package. I want to create a data frame ``melvin'' by reading in /mnt/cdrom/melvin.xls. What (in monosyllables --- step by step) do I do next? cheers, Rolf Turner rolf at math.unb.ca
The following works for me under WinXP Pro to create "myframe" as a data frame: library(RODBC) z <- odbcConnectExcel("c:/myfolder/mydata.xls") myframe <- sqlFetch(z, "Sheet1") close(z) Are you indicating the name of the worksheet you want within the *.xls file? I suspect there could be additional issues on a non-Windows OS that I don't know about. hope this helps, Chuck Cleland Rolf Turner wrote:> I gather from reading the back-issues of r-help that it should be > possible (modulo a number of caveats) to read an excel (yuck!) file > into R using RODBC. I have obtained and installed ODBC and the RODBC > package, but cannot for the life of me figure out how to go about > it. Can anyone give me a simple recipe? > > I have an excel file on cdrom, say: > > /mnt/cdrom/melvin.xls > > I have started R and loaded the RODBC package. I want to create > a data frame ``melvin'' by reading in /mnt/cdrom/melvin.xls. > What (in monosyllables --- step by step) do I do next?-- Chuck Cleland, Ph.D. NDRI, Inc. 71 West 23rd Street, 8th floor New York, NY 10010 tel: (212) 845-4495 (Tu, Th) tel: (732) 452-1424 (M, W, F) fax: (917) 438-0894
> library(RODBC) > z <- odbcConnectExcel("c:/myfolder/mydata.xls") > myframe <- sqlFetch(z, "Sheet1") > close(z)I found the reading of whole sheets somewhat unsafe, so I always create a named range (here: data) including header and do the following. Never had problems with this. channel = odbcConnectExcel("macronutrients.xls") ac = sqlQuery(channel,"select * from data") odbcClose(channel) Dieter
Chuck Cleland wrote:> The following works for me under WinXP Pro to create "myframe" as a > data frame: > > library(RODBC) > z <- odbcConnectExcel("c:/myfolder/mydata.xls") > myframe <- sqlFetch(z, "Sheet1") > close(z)I tried that and got the error message: Error: couldn't find function "odbcConnectExcel"> Are you indicating the name of the worksheet you want within the > *.xls file? I suspect there could be additional issues on a > non-Windows OS that I don't know about.On which Brian Ripley commented:> Most notably the absence of an Excel ODBC driver.I guess that's the problem. In my initial message I forgot to indicate that I am working on a Linux box. Sorry; mea culpa. It would appear then, that there is NO WAY to read Excel files into R save by transporting them to a Windoze system, saving them as .csv files and then transporting these back reading them into R. A bit unsatisfactory, but it ***is*** a workaround. Thanks to all who contributed advice/comments. cheers, Rolf Turner rolf at math.unb.ca
On Sat, 2004-12-04 at 14:32 -0400, Rolf Turner wrote:> Chuck Cleland wrote: > > > The following works for me under WinXP Pro to create "myframe" as a > > data frame: > > > > library(RODBC) > > z <- odbcConnectExcel("c:/myfolder/mydata.xls") > > myframe <- sqlFetch(z, "Sheet1") > > close(z) > > It would appear then, that there is NO WAY to read Excel files into R > save by transporting them to a Windoze system, saving them as .csv > files and then transporting these back reading them into R. A bit > unsatisfactory, but it ***is*** a workaround.To stay on Linux one possibility would be to use perl: http://www-106.ibm.com/developerworks/linux/library/l-pexcel/
Success! Tobias Verbeke's kind suggestion of read.xls from the gdata package (from the gregmisc bundle) works like a charm. It's perl based, so no problema on Linux. The R community is wonderful! cheers, Rolf Turner
There is another way to read and write excel files using jakarta POI. Hopefully, I'll have a package available in a week or so. I have a working example of writing a matrix from R to excel, but I haven't finished the read excel portion of the code. If anyone wants to give it a spin, contact me off list and I'll send a copy. http://jakarta.apache.org/poi/index.html Regards, Whit -----Original Message----- From: r-help-bounces at stat.math.ethz.ch [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Rolf Turner Sent: Saturday, December 04, 2004 2:43 PM To: r-help at stat.math.ethz.ch Subject: Re: [R] Excel *.xls files, RODBC Success! Tobias Verbeke's kind suggestion of read.xls from the gdata package (from the gregmisc bundle) works like a charm. It's perl based, so no problema on Linux. The R community is wonderful! cheers, Rolf Turner ______________________________________________ R-help at stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Rolf Turner <rolf <at> math.unb.ca> writes: : : I gather from reading the back-issues of r-help that it should be : possible (modulo a number of caveats) to read an excel (yuck!) file : into R using RODBC. I have obtained and installed ODBC and the RODBC : package, but cannot for the life of me figure out how to go about : it. Can anyone give me a simple recipe? : : I have an excel file on cdrom, say: : : /mnt/cdrom/melvin.xls : : I have started R and loaded the RODBC package. I want to create : a data frame ``melvin'' by reading in /mnt/cdrom/melvin.xls. : What (in monosyllables --- step by step) do I do next? The xlhtml program at http://freshmeat.net/projects/xlhtml/ can not only convert .xls to .html but also to .csv using something like: xlhtml -te -xc:1-10 -csv
I read excel spreadsheats into R often using the RODBC package. I like being able to manipulate my data in excel then import it directly into R without saving as text. I use a windows xp machine and an older version of R (1.9.1). Assuming you have a worksheet in melvin.xls named "data", here is the syntax I would use:>library(RODBC)>channel <- odbcConnectExcel("c:/mnt/cdrom/melvin.xls")>melvin <- sqlFetch(channel,"data")>odbcClose(channel)>detach("package:RODBC")Cheers, Alan On Sun, Dec 05, 2004 at 12:58:19PM +0000, Gabor Grothendieck wrote:> Rolf Turner <rolf <at> math.unb.ca> writes:>> :> : I gather from reading the back-issues of r-help that it should be> : possible (modulo a number of caveats) to read an excel (yuck!) file> : into R using RODBC. I have obtained and installed ODBC and the RODBC> : package, but cannot for the life of me figure out how to go about> : it. Can anyone give me a simple recipe?> :> : I have an excel file on cdrom, say:> :> : /mnt/cdrom/melvin.xls> :> : I have started R and loaded the RODBC package. I want to create> : a data frame ``melvin'' by reading in /mnt/cdrom/melvin.xls.> : What (in monosyllables --- step by step) do I do next?>> The xlhtml program at>> http://freshmeat.net/projects/xlhtml/>> can not only convert .xls to .html but also to .csv using something> like:>> xlhtml -te -xc:1-10 -csvAFAIK there are about three main strands of tools to deal with this in a manner that is platform-independent: -- Perl based on SpreadSheet::ReadExcel and OLE::StorageLite, which Greg has wrapped up nicely in gdata, a component of the gregmisc bundle -- C based libraries also used in Gnumeric, and, for that matter, also as a loadable module for GNU Gretl -- someone ambitious could add this to the foreign package; this may make for a nice term project. OpenOffice may have its own code base. -- Apache/Jakarta/POI based, which I know little of, but Whit just told us that he has something in the works there I think all three of these are maintained (in fact, I look after the Perl and Gretl ones for Debian). Where does xlhtml fit in? The code seems to be C based -- it this a split of the Gnumeric code? Have there been updates since 2002? Dirk -- If you don't go with R now, you will someday. -- David Kane on r-sig-finance, 30 Nov 2004 [[alternative HTML version deleted]]
There is also a perl module that converts excel files to .csv on CPAN. It works fine for everything I've ever used it for, which is really simple stuff, i.e. no cells defined by functions. steps involved: 1. go to www.cpan.org and find the package, download it 2. ensure you have the necessary setup to do things with perl, otherwise set them up 3. install the package 4. use it as the perl script instructs (i.e. <scriptname> --help or something similar) Cheers James