(R 1.9.1; Windows 2000;)
I'm just comparing ease of use, speed, etc for methods of transferring data frames in
the Excel, MySQL, R triangle. It turns out that going from Excel to R (when doing this
carefully). Using the clipboard is actually quite fast and efficient (2 seconds for
transferring 120 000 cells on a common desktop computer as compared to much longer for
going the RODBC route, maybe also substantially longer using the R(D)COM route). Other
advantage: Relatively flexible handling of missing values from Excel which may be
"empty", "#N/A", etc. So I thought I would also look at ways of going back via the
clipboard. There I'm hitting a funny snag. The documentation explains that there is a
32KB limit on writing to the clipboard. This may make sense as a default, but does it
make sense as a hard restriction? Any ideas of getting around this limitation (besides
cumbersome R-code buffering/breaking up the frame to send into small pieces to be
collected and assembled in Excel?
Chris
P.S. (some details for those who are interested)
Here is what I use to go from Excel to R (which in turn uses the RExcel add in to send
the R call, but not the data):
Range("Alldata").Copy
Call
RRun("Alldata<-read.table(file=file(description='clipboard'),sep='\t',na.strings=c('#N/A',''),header=TRUE,comment.char=';')")
There are two funny things about this:
1. It's very fast.
2. (this may be bug or feature depending on view point): Range("Alldata").Copy is
filter sensitive. So, if I use a common filter to select a part of Alldata in Excel,
executing the code will transfer only this part. If we turn this volatile and add a
few lines for some meaninful analysis which reports itself back or displays itself in
a graph, we arrive at an interesting level of animation).
Here is what I thought of using for going back:
write.table(Alldata,file=con<-file(description='clipboard',open="w"),sep='\t',na='#N/A',col.names=TRUE,row.names=FALSE);close(con)
And this works great if Alldata is small (less than 32KB when converted to text). It
no longer works if Alldata is larger (only the first 32KB go on the clipboard).
Christian Ritter
Functional Specialist Statistics
Shell Coordination Centre S.A.
Monnet Centre International Laboratory, Avenue Jean Monnet 1, B-1348 Louvain-La-Neuve,
Belgium
Tel: +32 10 477 349 Fax: +32 10 477 219
Email: [EMAIL PROTECTED]
Internet: http://www.shell.com/chemicals
[[alternative HTML version deleted]]
______________________________________________
[EMAIL PROTECTED] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
