Statistically Significant

Andrew Landgraf's Blog

Copying Data From Excel to R and Back

| Comments

A lot of times we are given a data set in Excel format and we want to run a quick analysis using R’s functionality to look at advanced statistics or make better visualizations. There are packages for importing/exporting data from/to Excel, but I have found them to be hard to work with or only work with old versions of Excel (*.xls, not *.xlsx). So for a one time analysis, I usually save the file as a csv and import it into R.

This can be a little burdensome if you are trying to do something quick and creates a file that needs to be cleaned up later. An easier option is to copy and paste the data directly into R. This can be done by using “clipboard” as the file and specifying that it is tab delimited, since that is how Excel’s clipboard stores the data.

For example, say you have a table in excel you want to copy into R. First, copy it in Excel.

Then go into R and use this function.

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

This function specifies that you are reading data from the clipboard, that it is tab delimited, and that it has a header.

Similarly, you can copy from R to Excel using the same logic. Here I also make row.name=FALSE as default since I rarely have meaningful row names and they mess up the header alignment.

write.excel <- function(x,row.names=FALSE,col.names=TRUE,...) {
write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
}
 
write.excel(dat)
Created by Pretty R at inside-R.org

These functions can be added to you .RProfile so that they are always ready for a quick analysis!

Obviously, this technique does not encourage reproducible research. It is meant to be used for quick, ad hoc analysis and plotting; not something you would use for an analysis that needs to be done on a regular basis.

Comments

Justin Tapp
I'm using this function in R for Windows. When I view the data numerically, it's fine. But when I go to plot the data I get nonsense. How to describe it…the window has 0 to 250 on the x-axis (regardless of my data series) and white boxes across the middle.
Marek Sz
I create similar functions and got few tips. For reading from excel following settings can be useful:
na.strings = "" # to prevent replacing NA string to missing value
comment.char = "" # to not loose everything after # sign
quote = "" # or ' or " could mess with data
check.names = FALSE # if you want column names as in excel (spaces, special characters, etc.). You need to use `column name` in R to reference such columns.

For writing na="" replace missing values by empty string and not "NA" as on default.

Second thing is that you can increase size of clipboard by using e.g. "clipboard-10240" instead of "clipboard" (it's a size in Kb, so it's around 10Mb; see help for connection, section Clipboard) which allow to copy and paste larger tables.
Anonymous
rkward (http://rkward.sourceforge.net/) has a very nifty feature (Edit -> Paste Special…), that allows you to paste the copied data directly into your R source code, already formatted as a single string, vector or matrix.
William Yarberry
Excellent article. Real people are always busy and this is just the kind of article that helps us all. I have been doing scan() but when you start entering a few thousand rows that way, it gets a bit slow.
Anonymous
scan() allows you to just paste…

y <- scan()
Anonymous
Thank you for these!!
Anonymous
Thank you for the tip, this will help me a lot.
Also, it seems that, libreOffice also uses clipboard to store copied things. This function also works for libreOffice
Anonymous
I can't resist: you could just use
read.delim("clipboard")

(The "clipboard" parameter is 'doze only for the foreseeable future)

From "?read.delim"
read.delim(file, header = TRUE, sep = "\t", quote="\"", dec=".",
fill = TRUE, comment.char="", …)
Anonymous
Thank you very much Tony for your quick answer (on a Sunday afternoon!!).

Ernesto
Tony Hirst
@Ernesto It seems that on a Mac, you can use pbpaste ( http://stackoverflow.com/questions/9035674/r-function-to-copy-to-clipboard-on-mac-osx )

read.clipboard.mac <- function(header=TRUE,…) {
read.table(pipe("pbpaste"),sep="\t",header=header,…)
}
Anonymous
Thank you very much. Very useful. I work in both Windows and Mac Environments. The trick you show seems to work only in Windows. Any idea what to do in Mac? Thanks in advance,

Ernesto

Comments