R help - Mar 2004 - How to read Excel file and access the data item?

In R, How to read Excel file and access the data item?
Thank you.


---------------------------------

	[[alternative HTML version deleted]]

On 3 Mar 2004, Grace Conlon verbalised:
> In R, How to read Excel file and access the data item?
> Thank you.
The catdoc package (http://www.45.free.net/~vitus/ice/catdoc/) includes
a program xls2csv that converts xls to csv (comma separated value) files.

In a Unix-like system, I often do

blah <- read.csv (pipe ("xls2csv somefile.xls"))

that saves the intermediate step of creating a csv file.  

An Excel file can have irregular cells (descriptions, graphs, etc) and
multiple sheets (xls2csv puts all sheets in one file, consecutively). You
need watch out for those and double check to make sure you are reading the
correct data.

I am not sure how good xls2csv is in handling xls files with formula
though.

Michael

Grace Conlon wrote:
> 
> In R, How to read Excel file and access the data item?
> Thank you.
> 
See R's Data Import/Export manual. At once, two solutions spring to
mind:

a) Export to csv or any other ASCII format and import in R.
b) RODBC


Uwe Ligges

There are a number of ways to do this:

1. select range including headers in Excel and
copy to clipboard (via ctrl-C) and in R:
mydata <- read.table("clipboard", header=T)

2. In Excel File | Save As and select csv as type.  In R:
mydata <- read.csv("/myfile.csv")

3. RODBC package
This package implements a Microsoft ODBC connection.

e.g.
      require(RODBC)
      z <- odbcConnectExcel("dd.xls")
      dd <- sqlFetch(z,"Sheet1")
      close(z)

4. RDCOMClient package

See http://www.omegahat.org/RDCOMClient/ 
This packages implemented a Microsoft COM connection.

5. Thomas Baier's R COM package

http://sunsite.univie.ac.at/rcom/
Similar to RDCOMClient.

6. Third party utilities

There are a number of free utilities for converting from
Excel to csv that you can find via googling.  

---
Date:   Wed, 3 Mar 2004 11:25:24 -0800 (PST) 
From:   Grace Conlon <gracestat at yahoo.com>
To:   <R-help at stat.math.ethz.ch> 
Subject:   [R] How to read Excel file and access the data item? 

 
In R, How to read Excel file and access the data item?
Thank you.

Hi

On 3 Mar 2004 at 11:25, Grace Conlon wrote:
> In R, How to read Excel file and access the data item?
> Thank you.
Quite strightforward way is to copy your data to notepad, save to 
a txt file and read this txt file to r by appropriate read.table() 
command.

Or you can use coppying through clipboard (with some 
restrictions) by this function.

readClip<- function(n=3, header=T)
{

vstup<-readClipboard()


if (header) {
jmena<-vstup[1]
vstup<-vstup[-1]
jmena<-as.character(unlist(strsplit(jmena,split="[[:space:]]")))

}

vystup<-
data.frame(matrix(as.numeric(unlist(strsplit(vstup,split="[[:space:]
]"))),length(vstup),n,byrow=T))

if (header) names(vystup)<-jmena

vystup

}

where n is number of columns you want to copy.





> 
> 
> ---------------------------------
> 
>  [[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
Petr Pikal
petr.pikal at precheza.cz