3 ways how to copy from R to Excel or from Excel to R

There might be useful to copy from R to Excel or vice versa, and here is how to do that. Of course, Excel is mentioned here because it is a popular use case for me. You can copy or paste in multiple other scenarios that involve R. You can copy from R to Word document if you want. There are multiple methods on how to do that, and here are some of them that if found the best.

copy from R to Excel and back with the clipr

Package clpr is my favorite choice that contains a set of simple functions that allow quickly copying or pasting data by using the clipboard.

If you want to copy from R to Excel spreadsheets it looks like this.

clipr::write_clip(head(airquality))

For reading your clipboard and pasting data from Excel into the R data frame, use function read_clip_tbl. It can be confused with read_clip that creates a character vector, and it is not what you want if you like to get a data frame in the result.

df <- clipr::read_clip_tbl()

If you are using this every time you work in RStudio, then load this package together with RStudio.

use base R capabilities or custom function

It is a little bit longer, but more customizable.

Here is how to copy from R and back by using the clipboard.

write.table(airquality, "clipboard", sep="\t", row.names=FALSE, na = "")

df <- read.table("clipboard", sep="\t", header = TRUE)

By using this approach, you can create a custom function to move your data between R to Excel.

try datapasta

The package datapasta has useful features that allow you to copy or paste data differently. In other words, if you want to paste your data into R as a reproducible data frame, then this is very handy.

I found it helpful to paste a vector for use in a script.

datapasta::vector_paste()

c("Ozone", "Solar.R", "Wind", "Temp", "Month", "Day")

the problem with copying from R to Excel

There might be situations when it looks like you cant copy from R to Excel. If there is something already copied in Excel and it is highlighted with dash border (looks like marching ants). Excel prefers that over something that comes from other applications.

In other words, Excel will use an office clipboard instead of what you are trying to copy from R. It might be annoying, but it looks like the best you can do is press the Esc button to disable marching ants in Excel.

Sometimes it is helpful to access Windows clipboard history, and here is a quick way how to do that.