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 bundle
    • Using readxl parcel
  • Importing Excel files using xlsx package
    • Installing and loading xlsx bundle
    • Using xlsx package
    • Read more
  • Summary
  • Related articles
  • Infos

Previously, we described the essentials of R programming and some all-time 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 well-nigh modern R package named readr, which is faster (X10) than R base functions.


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

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

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 information: select and copy the data (ctrl + c)

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

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

On Mac OSX arrangement

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

  2. Employ the function 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 package

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

Installing and loading readxl packet

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

Using readxl packet

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

  • Information technology'southward likewise 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 above in RStudio, y'all will be asked to cull a file.

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

Importing Excel files using xlsx package

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

Installing and loading xlsx parcel

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

Using xlsx packet

In that location 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=Truthful)

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

Example of usage:

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

Summary


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

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

Infos

This analysis has been performed using R (ver. 3.2.3).


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

Show me some love 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 diffusion en 50'envoyant par courriel à un ami ou en le partageant sur Twitter, Facebook ou Linked In.

Montrez-moi united nations peu d'amour avec les like ci-dessous ... Merci et n'oubliez pas, s'il vous plaît, de partager et de commenter ci-dessous!