# Moving data between R and Excel via the clipboard

These notes explain how to move data between R and Excel and other Windows applications via the clipboard.

## writeClipboard

R has a function `writeClipboard`

that does what the name implies.
However, the argument to `writeClipboard`

may need to be cast to a character type.
For example the code

> x <- "hello world" > writeClipboard(x)

copies the string "hello world" to the clipboard as expected. However the code

> x <- 3.14 > writeClipboard(x)

produces the error message

Error in writeClipboard(str, format) : argument must be a character vector or a raw vector

The solution is to call `writeClipboard( as.character(x) )`

, casting the
object `x`

to a character string.

All variables in R are vectors, and elements of a vector can have differing types.
If one element of a vector is a character string, all elements will be cast to
strings without the need for an explicit `as.character`

statement.
After a vector has been copied to the clipboard, the elements of the vector will
be separated by newlines when pasted into a document.

## readClipboard

The companion function for `writeClipboard`

is `readClipboard`

.

The command

x <- readClipboard()

will assign the contents of the clipboard to the vector `x`

. Each line
becomes an element of `x`

. The elements will be character strings, even if the clipboard
contained a column of numbers before the `readClipboard`

command was executed.
If you select a block of numbers from Excel, each row becomes a single string containing tabs
where there were originally cell boundaries.

## scan

You can use the `scan`

function to copy a column of numbers from
Excel to R. Copy the column from Excel, run `x <- scan()`

,
type Ctrl-v to paste into R, and press enter to signal the end of input to `scan`

.
Then `x`

will contain the numbers from Excel *as numbers*, not as quoted strings.
Note that `scan`

only works with columns of numbers. R will produce an error
message if the copied column contained a string. If there is an empty cell, only the
numbers above the first empty cell will be copied into the R vector.

Note that `scan`

works with **columns** in Excel.
If you copy a *row* of numbers from Excel and call `scan`

,
the numbers will be concatinated into a single number in R. For example, if you copy
horizontally adjacent cells containing 19 and 44 and run `x <- scan()`

,
then `x`

will contain 1944. To copy a row from Excel, first transpose
the row in Excel, then copy the result as a column.

The function `scan()`

is not limited to Excel. It could be used to
paste a column of numbers copied from other applications, such as Word or
Notepad.

## read.table and write.table

The functions above only work with columns of data; rows are combined into single entries. To move a block of cells from

The code `write.table(x, "clipboard", sep="\t")`

will copy a table `x`

to the clipboard in such a way that it can be pasted into Excel preserving the table structure.
By default, the row and column names will come along with the table contents. To leave the
row names behind, add the argument `row.names=FALSE`

to the call to `write.table`

.

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

Similarly, add `col.names=FALSE`

if you do not want the row names to come over to Excel.

write.table(x, "clipboard", sep="\t", row.names=FALSE, col.names=FALSE)

Other R resources:

The R Project

R for programmers

Five kinds of subscripts in R