Greetings everyone, I am new at R. My questions is rather basic. Looking R manuals looks like there should be a way to read MS excel files into R. Could somebody tell me which library should I use for that? Thanks in advance Victor H. Marín Laboratorio de Modelación Ecológica Depto. de Ciencias Ecológicas, Fac. de Ciencias, Universidad de Chile Casilla 653 Santiago, Chile http://antar.uchile.cl [[alternative HTML version deleted]]
On Tue, 24 Jun 2003, Victor H. Mar?m wrote:> I am new at R. My questions is rather basic. Looking R manuals looks > like there should be a way to read MS excel files into R. Could > somebody tell me which library should I use for that?Several ways are in the R Data Import/Export Manual (the obvious manual, I would have thought). If you are working on Windows, using RODBC is perhaps the simplest. -- 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 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
RODBC works fine but as far as I can tell requires that the connection be opened through Windows menus. OK for a one-off, but not for batch processing. Please someone tell me what I missed - how can I open the connection within an R script? (Windows 98)(not my fault) TIA (sorry for long disclaimer, can't switch it off) -----Original Message----- From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk] Sent: 24 June 2003 17:54 To: Victor H. Mar?m Cc: r-help at stat.math.ethz.ch Subject: Re: [R] excel files and R Security Warning: If you are not sure an attachment is safe to open please contact Andy on x234. There are 0 attachments with this message. ________________________________________________________________ On Tue, 24 Jun 2003, Victor H. Mar?m wrote:> I am new at R. My questions is rather basic. Looking R manuals looks > like there should be a way to read MS excel files into R. Could > somebody tell me which library should I use for that?Several ways are in the R Data Import/Export Manual (the obvious manual, I would have thought). If you are working on Windows, using RODBC is perhaps the simplest. -- 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 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 ______________________________________________ R-help at stat.math.ethz.ch mailing list https://www.stat.math.ethz.ch/mailman/listinfo/r-help Simon Fear Senior Statistician Syne qua non Ltd Tel: +44 (0) 1379 644449 Fax: +44 (0) 1379 644445 email: Simon.Fear at synequanon.com web: http://www.synequanon.com Number of attachments included with this message: 0 This message (and any associated files) is confidential and\ con... {{dropped}}
If you really want to run windows from R (in my experience it is much better to reside entirely within R) then I think that you need to use Duncan Temple Lang's RDCOM package ( http://www.omegahat.org/ ). It works well and robustly for connections to other packages and I have tested it for Excel. Regards, Gordon Morrison Global Head of Quantitative Research> * + 44 20 7653 7642 > Mob: + 44 7867 801951 > fax: + 44 20 7645 7442 > * mailto:gordon.morrison at commerzbankib.com > web: http://www.cbksec.com/rsh/portfolio&risk.html > * Commerzbank Securities > 60 Gracechurch Street > London EC3V 0HR, U.K. >-----Original Message----- From: Simon Fear [mailto:Simon.Fear at synequanon.com] Sent: 25 June 2003 09:22 To: r-help at stat.math.ethz.ch Subject: RE: [R] excel files and R RODBC works fine but as far as I can tell requires that the connection be opened through Windows menus. OK for a one-off, but not for batch processing. Please someone tell me what I missed - how can I open the connection within an R script? (Windows 98)(not my fault) TIA (sorry for long disclaimer, can't switch it off) -----Original Message----- From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk] Sent: 24 June 2003 17:54 To: Victor H. Mar?m Cc: r-help at stat.math.ethz.ch Subject: Re: [R] excel files and R Security Warning: If you are not sure an attachment is safe to open please contact Andy on x234. There are 0 attachments with this message. ________________________________________________________________ On Tue, 24 Jun 2003, Victor H. Mar?m wrote:> I am new at R. My questions is rather basic. Looking R manuals looks > like there should be a way to read MS excel files into R. Could > somebody tell me which library should I use for that?Several ways are in the R Data Import/Export Manual (the obvious manual, I would have thought). If you are working on Windows, using RODBC is perhaps the simplest. -- 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 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 ______________________________________________ R-help at stat.math.ethz.ch mailing list https://www.stat.math.ethz.ch/mailman/listinfo/r-help Simon Fear Senior Statistician Syne qua non Ltd Tel: +44 (0) 1379 644449 Fax: +44 (0) 1379 644445 email: Simon.Fear at synequanon.com web: http://www.synequanon.com Number of attachments included with this message: 0 This message (and any associated files) is confidential and\ con... {{dropped}} ______________________________________________ R-help at stat.math.ethz.ch mailing list https://www.stat.math.ethz.ch/mailman/listinfo/r-help ********************************************************************** This is a commercial communication from Commerzbank AG. This communication is confidential and is intended only for the person to whom it is addressed. If you are not that person you are not permitted to make use of the information and you are requested to notify <mailto:LONIB.Postmaster at commerzbankib.com> immediately that you have received it and then destroy the copy in your possession. Commerzbank AG may monitor outgoing and incoming e-mails. By replying to this e-mail you consent to such monitoring. This e-mail message and any attached files have been scanned for the presence of computer viruses. However, you are advised that you open attachments at your own risk. This email was sent either by Commerzbank AG, London Branch, or by Commerzbank Securities, a division of Commerzbank. Commerzbank AG is a limited liability company incorporated in the Federal Republic of Germany. Registered Company Number in England BR001025. Our registered address in the UK is 23 Austin Friars, London, EC2P 2JD. We are regulated by the Financial Services Authority for the conduct of investment business in the UK and we appear on the FSA register under number 124920.
> > There's a function odbcConnectExcel, a wrapper to odbcDriverConnect. > Neither require any work with Windows menus. >Try the following sample script and save it as "foo.R": library(RODBC) chan1 <- odbcConnectExcel("your-file.xls") aa <- sqlFetch(chan1, "Name of your sheet") names(aa) close(chan1) as batch: Rterm --no-restore --no-save < foo.R works for me, at least. HTH, Bernhard> On Wed, 25 Jun 2003, Simon Fear wrote: > > > RODBC works fine but as far as I can tell requires that the > connection > > be > > opened through Windows menus. OK for a one-off, but not for batch > > processing. > > Please someone tell me what I missed - how can I open the connection > > within > > an R script? > > (Windows 98)(not my fault) > > > > TIA > > (sorry for long disclaimer, can't switch it off) > > > > -----Original Message----- > > From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk] > > Sent: 24 June 2003 17:54 > > To: Victor H. Mar?m > > Cc: r-help at stat.math.ethz.ch > > Subject: Re: [R] excel files and R > > > > > > Security Warning: > > If you are not sure an attachment is safe to open please contact > > Andy on x234. There are 0 attachments with this message. > > ________________________________________________________________ > > > > On Tue, 24 Jun 2003, Victor H. Mar?m wrote: > > > > > I am new at R. My questions is rather basic. Looking R > manuals looks > > > like there should be a way to read MS excel files into R. Could > > > somebody tell me which library should I use for that? > > > > Several ways are in the R Data Import/Export Manual (the > obvious manual, > > I > > would have thought). If you are working on Windows, using RODBC is > > perhaps the simplest. > > > > > > -- > 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 272866 (PA) > Oxford OX1 3TG, UK Fax: +44 1865 272595 > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://www.stat.math.ethz.ch/mailman/listinfo/r-help >---------------------------------------------------------------------- If you have received this e-mail in error or wish to read our e-mail disclaimer statement and monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the sender.
Simon Fear wrote:> RODBC works fine but as far as I can tell requires that the connection > be > opened through Windows menus. OK for a one-off, but not for batch > processing. > Please someone tell me what I missed - how can I open the connection > within > an R script? > (Windows 98)(not my fault)[snip] It is no longer true since Pr Ripley provided a function odbcConnectExcel() (as well as odbcConnectAccess() and others) in the latest version of package RODBC. See the help file(s). Best, Renaud -- Dr Renaud Lancelot, v?t?rinaire CIRAD, D?partement Elevage et M?decine V?t?rinaire (CIRAD-Emvt) Programme Productions Animales http://www.cirad.fr/fr/pg_recherche/page.php?id=14 ISRA-LNERV tel +221 832 49 02 BP 2057 Dakar-Hann fax +221 821 18 79 (CIRAD) Senegal e-mail renaud.lancelot at cirad.fr
Many many thanks. I did look at this but I have absolutely no idea of
the
background so got completely lost. Can you recommend a gentle
introduction/overview to this area, based on the assumption that my
current
knowledge equals zero? Indeed, could you make an argument that I should
ever
*want* to run R from within Excel or vice versa? I think I just want to
get
the data from Excel ('cos that's how it nearly always comes), but I
don't
want to process it in Excel, when I have R ...
I guess all that I and apparently others really want is that "foreign"
might
include read.excel, like it has read.sas and read.spss. Which is
essentially
what Bernhard Pfaff's recent post offers - thanks again Bernhard - but
using
RODBC instead of foreign.
-----Original Message-----
From: Erich Neuwirth [mailto:erich.neuwirth at univie.ac.at]
Sent: 25 June 2003 10:42
To: Morrison, Gordon; r-help at stat.math.ethz.ch
Subject: Re: [R] excel files and R
And there is Thomas Baier's and my RCOM package
which would allow to run R from within Excel
or exchange data between Excel and R with R
as the main interface.
Morrison, Gordon wrote:> If you really want to run windows from R (in my experience it is much
better> to reside entirely within R) then I think that you need to use Duncan
Temple> Lang's RDCOM package ( http://www.omegahat.org/ ). It works well and
> robustly for connections to other packages and I have tested it for
Excel.
______________________________________________
R-help at stat.math.ethz.ch mailing list
https://www.stat.math.ethz.ch/mailman/listinfo/r-help
Simon Fear
Senior Statistician
Syne qua non Ltd
Tel: +44 (0) 1379 644449
Fax: +44 (0) 1379 644445
email: Simon.Fear at synequanon.com
web: http://www.synequanon.com
Number of attachments included with this message: 0
This message (and any associated files) is confidential and\...{{dropped}}
Simon,
Here is what I do when I encounter an excel spreadsheet. I save it as a
CSV file (in excel -> save as -> file type CSV) and then read the
corresponding file into R using read.csv.
Hope that helps,
Partha
"Simon Fear" <Simon.Fear at synequanon.com>
Sent by: r-help-bounces at stat.math.ethz.ch
06/25/2003 06:16 AM
To: "Erich Neuwirth" <erich.neuwirth at
univie.ac.at>
cc: r-help at stat.math.ethz.ch
Subject: RE: [R] excel files and R
Many many thanks. I did look at this but I have absolutely no idea of
the
background so got completely lost. Can you recommend a gentle
introduction/overview to this area, based on the assumption that my
current
knowledge equals zero? Indeed, could you make an argument that I should
ever
*want* to run R from within Excel or vice versa? I think I just want to
get
the data from Excel ('cos that's how it nearly always comes), but I
don't
want to process it in Excel, when I have R ...
I guess all that I and apparently others really want is that "foreign"
might
include read.excel, like it has read.sas and read.spss. Which is
essentially
what Bernhard Pfaff's recent post offers - thanks again Bernhard - but
using
RODBC instead of foreign.
-----Original Message-----
From: Erich Neuwirth [mailto:erich.neuwirth at univie.ac.at]
Sent: 25 June 2003 10:42
To: Morrison, Gordon; r-help at stat.math.ethz.ch
Subject: Re: [R] excel files and R
And there is Thomas Baier's and my RCOM package
which would allow to run R from within Excel
or exchange data between Excel and R with R
as the main interface.
Morrison, Gordon wrote:> If you really want to run windows from R (in my experience it is much
better> to reside entirely within R) then I think that you need to use Duncan
Temple> Lang's RDCOM package ( http://www.omegahat.org/ ). It works well and
> robustly for connections to other packages and I have tested it for
Excel.
______________________________________________
R-help at stat.math.ethz.ch mailing list
https://www.stat.math.ethz.ch/mailman/listinfo/r-help
Simon Fear
Senior Statistician
Syne qua non Ltd
Tel: +44 (0) 1379 644449
Fax: +44 (0) 1379 644445
email: Simon.Fear at synequanon.com
web: http://www.synequanon.com
Number of attachments included with this message: 0
This message (and any associated files) is confidential and\...{{dropped}}
______________________________________________
R-help at stat.math.ethz.ch mailing list
https://www.stat.math.ethz.ch/mailman/listinfo/r-help
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
I have to remove the dollar sign from the end of the sheet names.> > > > > There's a function odbcConnectExcel, a wrapper to odbcDriverConnect. > > Neither require any work with Windows menus. > > > Try the following sample script and save it as "foo.R": > > library(RODBC) > chan1 <- odbcConnectExcel("your-file.xls") > aa <- sqlFetch(chan1, "Name of your sheet") > names(aa) > close(chan1) > > as batch: > Rterm --no-restore --no-save < foo.R > > works for me, at least. > > > HTH, > Bernhard > > > > > > On Wed, 25 Jun 2003, Simon Fear wrote: > > > > > RODBC works fine but as far as I can tell requires that the > > connection > > > be > > > opened through Windows menus. OK for a one-off, but not for batch > > > processing. > > > Please someone tell me what I missed - how can I open the > connection > > > within > > > an R script? > > > (Windows 98)(not my fault) > > > > > > TIA > > > (sorry for long disclaimer, can't switch it off) > > > > > > -----Original Message----- > > > From: Prof Brian Ripley [mailto:ripley@stats.ox.ac.uk] > > > Sent: 24 June 2003 17:54 > > > To: Victor H. Marím > > > Cc: r-help@stat.math.ethz.ch > > > Subject: Re: [R] excel files and R > > > > > > > > > Security Warning: > > > If you are not sure an attachment is safe to open please contact > > > Andy on x234. There are 0 attachments with this message. > > > ________________________________________________________________ > > > > > > On Tue, 24 Jun 2003, Victor H. Marím wrote: > > > > > > > I am new at R. My questions is rather basic. Looking R > > manuals looks > > > > like there should be a way to read MS excel files into R. Could > > > > somebody tell me which library should I use for that? > > > > > > Several ways are in the R Data Import/Export Manual (the > > obvious manual, > > > I > > > would have thought). If you are working on Windows, > using RODBC is > > > perhaps the simplest. > > > > > > > > > > -- > > Brian D. Ripley, ripley@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 272866 (PA) > > Oxford OX1 3TG, UK Fax: +44 1865 272595 > > > > ______________________________________________ > > R-help@stat.math.ethz.ch mailing list > > https://www.stat.math.ethz.ch/mailman/listinfo/r-help > > > > > ---------------------------------------------------------------------- > If you have received this e-mail in error or wish to read our e-mail > disclaimer statement and monitoring policy, please refer to > http://www.drkw.com/disc/email/ or contact the sender. > > ______________________________________________ > R-help@stat.math.ethz.ch mailing list > https://www.stat.math.ethz.ch/mailman/listinfo/r-help >[[alternative HTML version deleted]]