How to copy the R data frame to Excel and customize your startup

Very often I use RStudio and Excel all together. In that situation, sometimes I want to copy some R results in the Excel spreadsheet, and, depending on the amount of data that I want to copy, there are different solutions to that process.

If the amount of data in the data frame is small, then it is easy to select necessary data with the cursor and copy it to the spreadsheet as usual (Ctrl+C, Ctrl+V). First, you have to view the data with command View() or by double-clicking on RStudio Environment pane data frame and then select it. If you select in a direction from the bottom up, then the result is without any offset.

If the amount of data in the data frame is too much for selecting with a cursor, then it is possible to make a function for that purpose. That function I found in this post.

write.excel <- function(x,row.names=FALSE,col.names=TRUE,...) {
  write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
}

There is also an option to copy data directly from an Excel spreadsheet to an R data frame. First, you have to copy it in Excel and then run this function in R.

read.excel <- function(header=TRUE,...) {
  read.table("clipboard",sep="\t",header=header,...)
}

If you want to use that solution very often, then the next thing is to add that custom function to R every time you start RStudio. It is possible to do by customizing your startup in Rprofile.site file. In my case that was located in C:\Program Files\R\R-3.4.3\etc.

Find your Rprofile.site file and open it in Notepad++ and paste your desired function in the next lines and save it.

If you need administrator privileges to do that and you don’t have any, there is an option to locate Rprofile.site in a different directory.
1. Choose the desired location.
2. Copy Rprofile.site to that location.
3. Make previous changes in Rprofile.site if necessary.
4. Close all R sessions.
5. Open Windows command line and set your R_USER profile.

SETX R_PROFILE_USER "C:/yourlocation/Rprofile.site"

When it been done, open R or RStudio and check the result in console with

Sys.getenv("R_PROFILE_USER")

If your custom function appears at Global Environment and you want to assign it to some package namespace, then modify the code with function assignInNamespace from package utils. In my case, I attached my custom function to the R base package.

utils::assignInNamespace(
"write.excel", 
	function(x,row.names=FALSE,col.names=TRUE,...) {
	write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
	}
, "base")

Warning message: clipboard buffer is full and output lost

If you’re getting this message, it means that data volume exceeded the limit that by default can be written to the clipboard. You can change this limit by modifying “clipboard”.

For example “clipboard-20000” if you need 20000 Kb limit.

write.excel <- function(x,row.names=FALSE,col.names=TRUE,...) {
  write.table(x,"clipboard-20000",sep="\t",row.names=row.names,col.names=col.names,...)
}