On Windows system

1. Open the Excel file containing your data: select and copy the data (ctrl + c)

2. Type the R code below to import the copied data from the clipboard into R and store the data in a data frame (my_data):

my_data <- read.table(file = "clipboard", 
                      sep = "\t", header=TRUE)

On Mac OSX system

1. Select and copy the data (Cmd + c)

2. Use the function pipe(pbpaste) to import the data you’ve copied (with Cmd + c):

my_data <- read.table(pipe("pbpaste"), sep="\t", header = TRUE)

Installing and loading readxl package

• Install

install.packages("readxl")

• Load

library("readxl")

Using readxl package

The readxl package comes with the function read_excel() to read xls and xlsx files

1. Read both xls and xlsx files

# Loading
library("readxl")
# xls files
my_data <- read_excel("my_file.xls")
# xlsx files
my_data <- read_excel("my_file.xlsx")

The above R code, assumes that the file “my_file.xls” and “my_file.xlsx” is in your current working directory. To know your current working directory, type the function getwd() in R console.

• It’s also possible to choose a file interactively using the function file.choose(), which I recommend if you’re a beginner in R programming:

my_data <- read_excel(file.choose())

If you use the R code above in RStudio, you will be asked to choose a file.

2. Specify sheet with a number or name

# Specify sheet by its name
my_data <- read_excel("my_file.xlsx", sheet = "data")
  
# Specify sheet by its index
my_data <- read_excel("my_file.xlsx", sheet = 2)

3. Case of missing values: NA (not available). If NAs are represented by something (example: “—”) other than blank cells, set the na argument:

my_data <- read_excel("my_file.xlsx", na = "---")

Installing and loading xlsx package

• Install

install.packages("xlsx")

• Load

library("xlsx")

Using xlsx package

There are two main functions in xlsx package for reading both xls and xlsx Excel files: read.xlsx() and read.xlsx2() [faster on big files compared to read.xlsx function].

The simplified formats are:

read.xlsx(file, sheetIndex, header=TRUE)
read.xlsx2(file, sheetIndex, header=TRUE)

Example of usage:

library("xlsx")
my_data <- read.xlsx(file.choose(), 1)  # read first sheet

Read more

Read more about for reading, writing and formatting Excel files:

• R xlsx package : A quick start guide to manipulate Excel files in R
• r2excel package: Read, write and format easily Excel files using R software