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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
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> -----Message d'origine----- > De : owner-r-help at stat.math.ethz.ch > [mailto:owner-r-help at stat.math.ethz.ch]De la part de Alistair Merrifield > Envoy? : Wednesday, January 10, 2001 10:12 PM > ? : 'r-help at lists.r-project.org' > Objet : [R] Reading Excel files from within R. > > > 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 > _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._. > _._._._._._._-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
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 -- According to the latest figures, 43% of all statistics are totally worthless. -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
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?> -----Original Message----- > From: owner-r-help at stat.math.ethz.ch > [mailto:owner-r-help at stat.math.ethz.ch]On Behalf Of Alistair Merrifield > Sent: Wednesday, January 10, 2001 7:12 PM > To: 'r-help at lists.r-project.org' > Subject: [R] Reading Excel files from within R. > > > 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 -- Readhttp://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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
On Thu, 11 Jan 2001, Alistair Merrifield wrote:> 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.Short answer: upgrade. 1.2.0 comes with a manual `R Data Import/Export' which describes all known ways to do this.> 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.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.> 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.Well, Excel is widely used on Windows. It is not popular with R developers. So if anyone wants to contribute code .... -- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272860 (secr) Oxford OX1 3TG, UK Fax: +44 1865 272595 -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
On Thu, 11 Jan 2001, Yves Gauvreau wrote:> 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.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 -- http://www.logilab.com Narval is the first software agent available as free software (GPL). LOGILAB, Paris (France). -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._