Skip to content

Reading Excel files from within R.

7 messages · Alistair Merrifield, Dirk Eddelbuettel, M. Edward (Ed) Borasky +3 more

Original
Alistair Merrifield
# 
Dear all,

I am interested in reading Excel spreadsheets (*.xls) directly into R (I am
using version 1.1.1 at the moment). I have not had much luck so far. 

For the time being, I have been converting Excel files to comma delimited
files (*.csv) and reading these into R (using commands such as "read.csv").
This works well, but it would be more efficient if I could read the Excel
files directly.

I have noticed that there is an Excel interface for R. However, this seems
to be a backwards approach to my problem . I would prefer to read Excel into
R, not use R in Excel!

I am sure this cannot be an uncommon thing to want to do (given that Excel
is a popular programme), although I have not had much success searching in
the CRAN archives for information. Any comments would be appreciated.

Cheers, Al.


Al Merrifield

Energy Analyst
Energy Modelling & Statistics
Ministry of Economic Development
PO Box 1473
Wellington
NEW ZEALAND

Phone: (04) 470 2338
Fax:     (04) 473 9930
E-mail: Alistair.Merrifield at med.govt.nz



-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Yves Gauvreau
# 
You already use the simplest method I can think of that is comma delimited
files.

I think we'll probably never see this as part of R for obvious reasons but
what would be very nice is to have an ActiveX interface. That is the
possibility of creating an Ole object and using it's properties and methods.

The next best thing I can think of would be to use an intermediary DLL that
would create and use Ole objects (such as Excel) and transfer the data you
need and/or do whatever else you could think of. But it would be a lot of
work just to transfer data back and forth.

Maybe a much simpler approach would be to use a vbscript | jscript |
perl-script. Any of these is Ole aware and you could write an R function to
run your script which would extract the data you want, put it in a file that
R can read back. That is the quickest and easiest expedient you could use to
control everything from R.

Regards.

Yves Gauvreau
B.E.F.P. Universit? du Qu?bec ? Montr?al
cyg at sympatico.ca
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Dirk Eddelbuettel
# 
Alistair>  Dear all, I am interested in reading Excel spreadsheets (*.xls)
  Alistair> directly into R (I am using version 1.1.1 at the moment). I have
  Alistair> not had much luck so far.

As you probably know, Excel is not exactly an open source program. Further,
there is little available documentation on the .xls file format, and no
guarantee whatsoever that it won't change once you figured one version out.
In short, not a promising avenue for open source.

That being said, there are few choices

a) a little bit of alpha-stage code in the CPAN (i.e. Perl) archives for both
reading (Spreadsheet-ParseExcel) and writing Excel (Spreadsheet-WriteExcel,
this also has a few more references) spreadsheets

b) the ActiveState Perl implementation for Windows can read .xls files by
"driving" Excel through OLE, this does of course require Excel on a Windoze
machine

c) a little while ago Brian Ripley posted on the list how he used ODBC to
read from .xls files 

d) lastly, the Gnumeric spreadsheet knows how to read .xls files, you could try
to learn from its code and create a R function read.xls if you truly need
this.

Take your pick. Saving as .csv still seems like the easiest solution to me
though I also do a bit of b).

Hope this helps,  Dirk
M. Edward (Ed) Borasky
# 
Try setting up an ODBC interface to your spreadsheet. In the Control Panel
(Win2K) open Administrative Tools, then Data Sources (ODBC). Select the File
DSN tab and do an ADD. You'll be able to attach a Data Source Name (DSN) to
your Excel spreadsheet. After you have this done, you'll be able to get data
from the spreadsheet with an ODBC query.

Then you need to install the R-ODBC package. I haven't done this part
myself, but I have done the other part that I just described. The R-ODBC
package will let you do queries against the ODBC name you just defined.
--
M. Edward Borasky
Borasky Research
http://www.borasky-research.com
mailto:znmeb at borasky-research.com

If there's nothing to astrology, how come so many famous men were born on
holidays?
http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._.
_._


-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Brian Ripley
#
On Thu, 11 Jan 2001, Alistair Merrifield wrote:

            

      
      
      
    

        
Short answer: upgrade. 1.2.0 comes with a manual `R Data Import/Export'
which describes all known ways to do this.

            

      
      
      
    

        
You don't actually say what platform this is on.  Excel is a proprietary
format, and I know people who have tried hard and failed to read it on
other platforms.

            

      
      
      
    

        
Well, Excel is widely used on Windows.  It is not popular with R
developers.  So if anyone wants to contribute code ....

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Alexandre Fayolle
    

    

      
      #
    

  


  

      
On Thu, 11 Jan 2001, Yves Gauvreau wrote:

        

            

      
      
      
    

        
I heard that R was Python-aware. Python on windows has some COM modules
that could be used to do just that without changing R. 


Alexandre Fayolle

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Yves Gauvreau
    

    

      
      #
    

  


  

          

      
      
      
    

        
I forgot, if it's just reading and writing Excel files you want RODBC is
every thing you nead.

YG

            

      
      
      
    

        
http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._.
_._

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._