Reading Data From Excel Files (xls|xlsx) into R – Easy Guides – Wiki – STHDA

previously, we described the essentials of R programming and some best practices for preparing your data. We besides provided agile start guides for read and writing txt and csv files using R base functions ampere well as using a most modern R package named readr, which is faster ( X10 ) than R infrastructure functions .

In this article, you ’ ll learn how to read data from Excel xls or xlsx charge formats into R. This can be done either by :

  • copying data from Excel
  • using readxl package
  • or using xlsx package

Reading Data From Excel Files (xls|xlsx) into R

Copying data from Excel and import into R

On Windows system

  1. Open the Excel file containing your data : select and copy the data ( ctrl + deoxycytidine monophosphate )
  2. Type the R code below to import the replicate data from the clipboard into R and store the data in a data frame ( my_data ) :

On Mac OSX system

  1. Select and copy the data ( Cmd + degree centigrade )
  2. Use the serve pipe(pbpaste) to import the datum you ’ ve copied ( with Cmd + c ) :

Importing Excel files into R using readxl package

The readxl package, developed by Hadley Wickham, can be used to easily import Excel files ( xls|xlsx ) into R without any external dependencies .

Installing and loading readxl package

  • Install
  • Load

Using readxl package

The readxl box comes with the affair read_excel ( ) to read xls and xlsx files

  1. Read both xls and xlsx files
# Loading
# xls files

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

  • 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:

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

  1. Specify sheet with a number or name
# Specify sheet by its name
  1. Case of missing values: NA (not available). If NAs are represented by something (example: “—”) other than blank cells, set the na argument:

Importing Excel files using xlsx package

The xlsx box, a java-based solution, is one of the knock-down R packages to read, write and format Excel files .

Installing and loading xlsx package

  • Install
  • Load

Using xlsx package

There are two main functions in xlsx software 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)

  • file: file path
  • sheetIndex: the index of the sheet to be read
  • header: a logical value. If TRUE, the first row is used as column names.

model of custom :


Read more

Read more about for recitation, 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


  • Read Excel files using readxl software : read_excel ( file.choose ( ), sheet = 1 )
  • Read Excel files using xlsx package: read.xlsx(file.choose(), sheetIndex = 1)


This psychoanalysis has been performed using R ( five. 3.2.3 ) .

generator :
Category : Lastest

Related Posts

Leave a Reply

Your email address will not be published.