thr3ads.net - R help - [R] Excel *.xls files, RODBC [Dec 2004]

If this information is useful, please help other people find it:
Share via:

Rolf Turner

2004-Dec-04 14:49 UTC

[R] Excel *.xls files, RODBC

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

Chuck Cleland

2004-Dec-04 15:10 UTC

head link

[R] Excel *.xls files, RODBC

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

Dieter Menne

2004-Dec-04 17:26 UTC

head link

[R] Excel *.xls files, RODBC

> 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

Rolf Turner

2004-Dec-04 18:32 UTC

head link

[R] Excel *.xls files, RODBC

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

Rajarshi Guha

2004-Dec-04 18:38 UTC

head link

[R] Excel *.xls files, RODBC

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/

Rolf Turner

2004-Dec-04 19:43 UTC

head link

[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

Whit Armstrong

2004-Dec-05 04:25 UTC

head link

[R] Excel *.xls files, RODBC

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

Gabor Grothendieck

2004-Dec-05 12:58 UTC

head link

[R] Excel *.xls files, RODBC

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

Alan Swanson

2004-Dec-06 16:42 UTC

head link

[R] Excel *.xls files, RODBC

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

AFAIK 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]]

James Muller

2004-Dec-06 17:09 UTC

head link

[R] Excel *.xls files, RODBC

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

Possibly Parallel Threads

Search for more apparently analagous threads

R help - Dec 2004 - Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

[R] Excel *.xls files, RODBC

Possibly Parallel Threads