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


