R help - Oct 2010 - Read Particular Cells within Excel

Hello all,

I have a business user who generates monthly reports in MS Excel in a particular
format. The data I need is present in different portions of this excel file. Is 
there a way to read different cells from a particular excel worksheet? i.e., 
cells b50:d100 in the Inputs worksheet. I am investigating odbcConnectExcel but 
did not yet see such capability. 


Appreciate your help.

Jeevan


      
	[[alternative HTML version deleted]]

I don't know of any tools that would do this generally (RODBC could do it if
there were specific column names in the 1st row and a given column with
information to identify the rows, but this seems unlikely from your
description).  A couple of possibilities:

In Excel you can highlight the columns of interest, right click and choose
'copy', then in R type:
> mydata <- read.delim('clipboard')
And you will have the data.  This is easy and straight forward, but requires
clicking and therefore cannot be automated.


The read.xls function in the gdata package uses Perl to extract data from an
excel file and import it into R, it has an option for setting the beginning row
to read from, but not the general section that you ask for.  The Perl tools
however can grab specific ranges of cells, you could adapt the read.xls function
(and Perl script) to do what you want in a more automated way.

Good luck,

-- 
Gregory (Greg) L. Snow Ph.D.
Statistical Data Center
Intermountain Healthcare
greg.snow at imail.org
801.408.8111

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-
> project.org] On Behalf Of Jeevan Duggempudi
> Sent: Tuesday, October 12, 2010 6:17 PM
> To: r-help at r-project.org
> Subject: [R] Read Particular Cells within Excel
> 
> Hello all,
> 
> I have a business user who generates monthly reports in MS Excel in a
> particular
> format. The data I need is present in different portions of this excel
> file. Is
> there a way to read different cells from a particular excel worksheet?
> i.e.,
> cells b50:d100 in the Inputs worksheet. I am investigating
> odbcConnectExcel but
> did not yet see such capability.
> 
> 
> Appreciate your help.
> 
> Jeevan
> 
> 
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-
> guide.html
> and provide commented, minimal, self-contained, reproducible code.

Try this:

library(RDCOMClient)
xl <- COMCreate("Excel.Application")
wk <- xl$Workbooks()$Open("Book1.xlsx")
do.call(cbind, wk$Sheets(1)$Range("B50:C55")$Value())



On Tue, Oct 12, 2010 at 9:17 PM, Jeevan Duggempudi
<jdugge1@yahoo.com>wrote:
> Hello all,
>
> I have a business user who generates monthly reports in MS Excel in a
> particular
> format. The data I need is present in different portions of this excel
> file. Is
> there a way to read different cells from a particular excel worksheet?
> i.e.,
> cells b50:d100 in the Inputs worksheet. I am investigating odbcConnectExcel
> but
> did not yet see such capability.
>
>
> Appreciate your help.
>
> Jeevan
>
>
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O

	[[alternative HTML version deleted]]