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 system
  • Importing Excel files into R using readxl parcel
    • Installing and loading readxl parcel
    • 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, we described the essentials of R programming and some best practices for preparing your data. We besides provided quick start guides for reading and writing txt and csv files using R base of operations functions as well as using a most modern R packet named readr, which is faster (X10) than R base of operations functions.


In this commodity, you'll learn how to read data from Excel xls or xlsx file formats into R. This tin be washed either by:

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

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

Copying information from Excel and import into R

On Windows system

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

  2. Type the R code below to import the copied data from the clipboard into R and store the information 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 part pipe(pbpaste) to import the data you've copied (with Cmd + c):

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

Importing Excel files into R using readxl packet

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

Installing and loading readxl bundle

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

Using readxl package

The readxl bundle 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 lawmaking, assumes that the file "my_file.xls" and "my_file.xlsx" is in your electric current working directory. To know your electric current working directory, type the function getwd() in R console.

  • It'due south besides possible to choose a file interactively using the office file.choose(), which I recommend if you lot're a beginner in R programming:
                    my_data <- read_excel(file.choose())

If y'all use the R code to a higher place in RStudio, you will exist asked to cull a file.

  1. Specify canvass with a number or name 
                    # Specify canvass by its name my_data <- read_excel("my_file.xlsx", canvas = "data")    # Specify sheet past its alphabetize my_data <- read_excel("my_file.xlsx", canvass = 2)
  1. Instance of missing values: NA (not available). If NAs are represented by something (case: "—") other than blank cells, set up the na statement:
                    my_data <- read_excel("my_file.xlsx", na = "---")

Importing Excel files using xlsx packet

The xlsx package, a java-based solution, is one 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 parcel

There are two main functions in xlsx bundle for reading both xls and xlsx Excel files: read.xlsx() and read.xlsx2() [faster on large 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 exist read
  • header: a logical value. If TRUE, the outset row is used as cavalcade names.

Example of usage:

                    library("xlsx") my_data <- read.xlsx(file.cull(), 1)  # read first sail

Summary


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

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

Infos

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


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

Show me some honey with the similar buttons below... Thank you and please don't forget to share and annotate below!!

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

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