Getting data from Excel to R-script, using Windows clipboard

Author: Žaneta Vaníčková

This article describes how to simply load data from Excel table to R script environment.

We´re going to use these two commands:

data <- readClipboard()

data <- read.table(file = 'clipboard', sep = "\t", header = TRUE, stringsAsFactors = FALSE)

We are getting the data for R analysis very often in .xlsx files. There are many packages enabling us to export / import to / from Excel to R. But, anyway, getting data from Excel isn´t too easy usually. Some users are saving the data from Excel to .csv and importing after.
So in this tutorial, we will show how to get the data from Excel easily – using the Windows clipboard.

Let´s start.
You can use any Excel file. Select all data you want to copy and copy it (Ctrl + C).

Open R studio. Now you need to paste. This can be done by this command, importing data from Windows clipboard:

data <- readClipboard()

head(data)

Head() function shows first few rows only.

> data <- readClipboard()
> 
> head(data)
[1] "Brand\tModel\tPrice\tColor\tOn Stock from:" "Alfa Romeo\tSpider\t 207 000 Kč \tSilver\t08.10.2017"
[3] "Fiat\tCoupe\t 187 000 Kč \tBlue\t08.10.2017" "Citroen\tPluriel\t 44 000 Kč \tSilver\t09.10.2017" 
[5] "Citroen\tPluriel\t 208 000 Kč \tSilver\t10.10.2017" "Alfa Romeo\t156\t 280 000 Kč \tSilver\t10.10.2017"

To see complete data, write this instead:

data <- readClipboard()
data

Columns in Excel are separated by tab (\t). The readClipboard() function itself can´t work with delimiter, it is mostly mused for quick import of row data. So to load of data the read.table() function should be used.

data <- read.table(file = 'clipboard', sep = "\t", header = TRUE, stringsAsFactors = FALSE) 
head(data)

  Brand Model Price Color On.Stock.from.
1 Alfa Romeo Spider 207 000 Kč Silver 08.10.2017
2 Fiat Coupe 187 000 Kč Blue 08.10.2017
3 Citroen Pluriel 44 000 Kč Silver 09.10.2017
4 Citroen Pluriel 208 000 Kč Silver 10.10.2017
5 Alfa Romeo 156 280 000 Kč Silver 10.10.2017
6 Skoda Favorit 30 000 Kč Red 11.10.2017

Explanation of input parameters of read.table() function:

  • file = ‘clipboard’ – the source of data is the clipboard
  • sep = “\t” – tab is used as delimiter
  • header = TRUE – the first row contains headers of columns
  • stringsAsFactors = FALSE – we´re not using “factor” datatype

1 Comment

  1. Democrats Ready Impeachment Charge Against Trump for Inciting Capitol Mob
    Speaker Nancy Pelosi threatened decisive action against the president for his role in the insurrection against Congress if he refused to resign.

Comments are closed.