I was looking at this as well.
I tried using ODBC, but to no avail.
However, it does work in R via their RODBC library, for what it's worth.
Sincerely,
Laura Holt
mailto: lauraholt_983@hotmail.com
From: "Thompson, Trevor" <tkt2@cdc.gov>
To: "Pravin Jadhav" <pravinj@gmail.com>
CC: <s-news@lists.biostat.wustl.edu>
Subject: Re: [S] Exporting to Excel spreadsheet with multiple worksheets
Date: Thu, 14 Apr 2005 09:12:07 -0400
Thanks for the response. It appears that the options for exporting data
to Excel are more limited than the import options. I first attempted to
do this with exportData but didn't see a way to export to multiple
sheets since there was no pageNumber argument like there is in
importData. I thought the openData function would work, but it seems
that the pageNumber argument does not work for openType="write". This
seems consistent with the lack of the pageNumber argument in exportData
but I can't find any documentation to confirm this.
-----Original Message-----
From: Pravin Jadhav [mailto:pravinj@gmail.com]
Sent: Wednesday, April 13, 2005 9:53 PM
To: Thompson, Trevor
Cc: s-news@lists.biostat.wustl.edu
Subject: Re: [S] Exporting to Excel spreadsheet with multiple
worksheets
here are some messages that I received to a similar query.
http://www.biostat.wustl.edu/archives/html/s-news/2004-12/msg00011.html
MESSAGE 1
---------- Forwarded message ----------
From: Austin, Matt <maustin@amgen.com>
Date: Dec 2, 2004 4:55 PM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data
frame
To: Pravin Jadhav <pravinj@gmail.com>
Here is a section of code that might be helpful, the first
section reads all
the pages into a list of dataframes and adds a column that
specifies the
page by number to each dataframe. The the do.call functoin
concatenates it
all together.
schoolData <- lapply(1:8, function(x)
data.frame(Page=x,
importData("c:/data_mirror/ElementaryMathFall041.xls",
page=x,
stringsAsFactors=FALSE)))
school.df <- do.call('rbind', schoolData)
Matt Austin
Statistician
MESSAGE 2
---------- Forwarded message ----------
From: Biggerstaff, Brad J. <BKB5@cdc.gov>
Date: Dec 2, 2004 5:07 PM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data
frame
To: Pravin Jadhav <pravinj@gmail.com>
Perhaps not pretty, but if you know the number of pages, you can
use
importData with a for loop:
> x.dat <-
importData(file="c:/TestImport.xls",type="excel",pageNumber=1)
> for(i in 2:2) x.dat <-
rbind(x.dat,importData(file="c:/TestImport.xls",type="excel",pageNumber=
i))
where there are just 2 pages here.
This does assume the worksheets are, as you say, "the same"...as
is the
example workbook I include.
Cheers,
Brad
Brad Biggerstaff, Ph.D .
(970) 221-6473 ... BBiggerstaff@cdc.gov
MESSAGE 3
---------- Forwarded message ----------
From: Molinari, Luciano <Luciano.Molinari@kispi.unizh.ch >
Date: Dec 3, 2004 4:48 AM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data
frame
To: Pravin Jadhav <pravinj@gmail.com>
SPLUS2K under W2K
Select:
File/Import Data/From File/
Then the Option submenu allows to select the page.
Alternatively, working from the command window, the function
import.data
has an undocumented parameter "PageNumber" which allows to input
different
Excel work sheets, which can then be combined into a a single
data frame.
You can try both ways on the attached Excel file XY.
From the command window:
import.data(DataFrame="zz1",FileName="
XY.xls",FileT="EXCEL",PageNumber=1)
import.data(DataFrame="zz2",FileName="XY.xls",FileT="EXCEL",PageNumber=2
)
Hope this helps,
L. Molinari
On 4/13/05, Thompson, Trevor <tkt2@cdc.gov> wrote:
Hi,
I'm trying to write out matrices within a loop to
different worksheets
in the same Excel spreadsheet. I was trying to do this
with the
openData (using the pageNumber argument to specify the
worksheet) and
writeNextDataRows functions. When I do this, it only
writes to one
worksheet and overwrites the data as the loop
increments. I am able to
read from multiple worksheets ok. See examples below:
### Creates 3 dataframes from 3 worksheets in Excel file
for(i in 1:3) {
out.file <- openData("test.xls", type="Excel",
openType="read",
pageNumber = i)
if(i==1) test.df1<-readNextDataRows(out.file)
if(i==2) test.df2<-readNextDataRows(out.file)
if(i==3) test.df3<-readNextDataRows(out.file)
closeData(out.file)
}
#### Does not write to multiple sheets; overwrites 1st
sheet
for(i in 1:3) {
out.file <- openData("test2.xls", type="Excel",
openType="write",
pageNumber = i)
x <- matrix(rnorm(50), ncol=5)
writeNextDataRows(out.file, x)
closeData(out.file)
}
Does the pageNumber argument only work for
openType="read"? Is there
any other way to export to multiple worksheets?
Thanks for any advice. I am using Splus 6.1 on MS
Windows 2000.
-Trevor
--------------------------------------------------------------------
This message was distributed by
s-news@lists.biostat.wustl.edu. To
unsubscribe send e-mail to
s-news-request@lists.biostat.wustl.edu with
the BODY of the message: unsubscribe s-news
--
Pravin Jadhav
Graduate Student
Department of Pharmaceutics
MCV/Virginia Commonwealth University
DPE1/OCPB/CDER/Food and Drug Administration
Phone: (301) 594-5652
Fax: (301) 480-3212
/_/_/_/_
If you have an apple and I have an apple and we exchange these
apples then you and I will still each have one apple. But if you have an
idea and I have an idea and we exchange these ideas, then each of us
will have two ideas.
--George Bernard Shaw
_________________________________________________________________
Don?t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
|