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

  • Preleminary tasks
  • Copying data from Excel and import into R
    • On Windows system
    • On Mac OSX arrangement
  • Importing Excel files into R using readxl package
    • Installing and loading readxl package
    • Using readxl package
  • Importing Excel files using xlsx package
    • Installing and loading xlsx packet
    • Using xlsx package
    • Read more than
  • Summary
  • Related articles
  • Infos

Previously, nosotros described the essentials of R programming and some all-time practices for preparing your data. We also provided quick start guides for reading and writing txt and csv files using R base of operations functions also equally using a nigh modern R parcel named readr, which is faster (X10) than R base functions.


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

  • copying data from Excel
  • using readxl packet
  • 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 information: select and copy the data (ctrl + c)

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

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

On Mac OSX system

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

  2. Employ the function pipe(pbpaste) to import the information y'all've copied (with Cmd + c):

                    my_data <- read.tabular array(piping("pbpaste"), sep="\t", header = TRUE)

Importing Excel files into R using readxl parcel

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

Installing and loading readxl packet

  • Install
                    install.packages("readxl")
  • Load
                    library("readxl")

Using readxl package

The readxl parcel comes with the office 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 as well 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 employ the R code in a higher place in RStudio, you will be asked to cull a file.

  1. Specify canvass with a number or name 
                    # Specify canvas by its proper name my_data <- read_excel("my_file.xlsx", sheet = "data")    # Specify sail by its index my_data <- read_excel("my_file.xlsx", sheet = 2)
  1. Case of missing values: NA (non bachelor). If NAs are represented by something (example: "—") other than blank cells, set the na argument:
                    my_data <- read_excel("my_file.xlsx", na = "---")

Importing Excel files using xlsx package

The xlsx package, a coffee-based solution, is 1 of the powerful R packages to read, write and format Excel files.

Installing and loading xlsx package

  • Install
                    install.packages("xlsx")
  • Load
                    library("xlsx")

Using xlsx package

There are ii main functions in xlsx parcel for reading both xls and xlsx Excel files: read.xlsx() and read.xlsx2() [faster on large files compared to read.xlsx part].

The simplified formats are:

                    read.xlsx(file, sheetIndex, header=Truthful) 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 beginning row is used as column names.

Example of usage:

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

Summary


  • Read Excel files using readxl packet: read_excel(file.choose(), sail = 1)

  • Read Excel files using xlsx packet: read.xlsx(file.choose(), sheetIndex = one)

Infos

This analysis has been performed using R (ver. three.2.3).


Enjoyed this article? I'd be very grateful if yous'd help it spread by emailing it to a friend, or sharing it on Twitter, Facebook or Linked In.

Show me some dearest with the like buttons below... Give thanks you and delight don't forget to share and comment below!!

Avez vous aimé cet article? Je vous serais très reconnaissant si vous aidiez à sa diffusion en fifty'envoyant par courriel à united nations ami ou en le partageant sur Twitter, Facebook ou Linked In.

Montrez-moi un peu d'amour avec les similar ci-dessous ... Merci et n'oubliez pas, due south'il vous plaît, de partager et de commenter ci-dessous!