1

I sometimes have to copy data from Excel into R. The workflow goes something like this:

# Step 1: Highlight Excel spreadsheet to be copied into R
# Step 2: Run this command to get the data into R
excelss <- read.delim("clipboard")  # for Windows

If I print(excelss) I get my data frame

  Excel.Col.1  Excel.Col.2
1           A           24
2           B            5
3           C           53

The question is: How do I take this data frame output, and permanently save it in my script? What reprex commands do I use? So that the next time I open the script the data frame will be right there, and I don't have to open Excel and go through the whole copy/paste routine again?

Or another way to put it. How do I take console data frame output and save it to my editor?

stackinator
  • 5,429
  • 8
  • 43
  • 84
  • Use `read.table(header = TRUE, sep = "\t", quote = "\"", dec = ".", fill = TRUE, comment.char = "", text="...")` i.e. other parameters beside `text=` are set as in `read.delim()` Usually I use `read.table(header=TRUE, text="...")` – jogo Nov 21 '18 at 13:03
  • Why can't you use `readxl::read_excel()`? Copy/paste does not lend itself to reproducible workflows (nor does it facilitate eventual automation/scripting). – hrbrmstr Nov 21 '18 at 13:14
  • @hrbrmstr for my case I want the data directly in the script and don't want to have to reference the excel file the data originally came from or load any packages on top of base-R. I work with sensitive data and the Excel file (and that workflow you mention) causes issues with security (if I can leave it at that). – stackinator Nov 21 '18 at 13:19
  • @jogo can you put that in the answer format? I can't quite follow what you mean. Is `text="..."` shorthand for *copy your console* output to this space? I tried that but the row numbers the console print screws everything up. Thanks – stackinator Nov 21 '18 at 13:39

2 Answers2

3

I like working with the library(datapasta). It adds an addin to RStudio which enables you to paste tabular data as a data.frame definition (also other outputs possible e.g. vector). After installing the package it is available via the Addins-dropdown menu in RStudio.

result

ismirsehregal
  • 30,045
  • 5
  • 31
  • 78
1

Use read.table(header = TRUE, sep = "\t", quote = "\"", dec = ".", fill = TRUE, comment.char = "", text="...") i.e. other parameters beside text= are set as in read.delim() Usually I use read.table(header=TRUE, text="..."), e.g. for your data:

excelss <- read.table(header=TRUE, text=
"      Excel.Col.1  Excel.Col.2
               A           24
               B            5
               C           53")

or 

excelss <- read.table(header=TRUE, text=
"  Excel.Col.1  Excel.Col.2
1           A           24
2           B            5
3           C           53")
excelss
jogo
  • 12,469
  • 11
  • 37
  • 42