Thursday, December 20, 2012

Transferring Data Between R and Excel

For a long time, I assumed that the only way to transfer data between R and Excel was to do something like the following:

write.csv(x=some.data.frame, file="some_file.csv")

and then to open the resulting CSV file in Excel.  I didn't like doing this, because by the end of an analysis I would have a bunch of temporary "deleteme.csv" files cluttering my Desktop.

It turns out there is a better way to move data between R and Excel:

Move Data from R to Excel

First, from R:
write.table(x=some.data.frame, file="clipboard", sep="\t")

This command copies the specified data frame (not sure how well it works with other object types) to the system clipboard.

Then all one needs to do is open a new workbook in Excel and Ctrl-V (paste) the data into the workbook!  Easy as pie!

Move Data from Excel to R

First, in Excel, select the data you would like to copy, then copy it (Ctrl-C).

Then, in R:
data.from.excel <- read.table(file="clipboard", sep="\t")

That's all there is to it.  This will allow you to quickly and easily transfer data between R and Excel, or more generally, between R and any program that can read data from the clipboard.