(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: christian.ritter@shell.com
Internet: http://www.shell.com/chemicals


	[[alternative HTML version deleted]]


