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

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

Previously, we described the essentials of R programming and some best practices for preparing your data. We also provided quick start guides for reading and writing txt and csv files using R base functions too as using a nearly modern R package named readr, which is faster (X10) than R base functions.


In this commodity, you'll learn how to read data from Excel xls or xlsx file formats into R. This can exist washed 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 up the Excel file containing your data: select and re-create the information (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.table(file = "clipboard",                        sep = "\t", header=Truthful)

On Mac OSX system

  1. Select and re-create the data (Cmd + c)

  2. Utilize the office pipage(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 packet, developed past Hadley Wickham, can be used to hands import Excel files (xls|xlsx) into R without any external dependencies.

Installing and loading readxl package

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

Using readxl packet

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

  • Information technology's too possible to cull a file interactively using the function file.choose(), which I recommend if y'all're a beginner in R programming:
                    my_data <- read_excel(file.cull())

If you lot use the R lawmaking higher up in RStudio, you lot will be asked to choose a file.

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

Importing Excel files using xlsx bundle

The xlsx packet, a coffee-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 package

At that place are ii 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)

  • file: file path
  • sheetIndex: the index of the sheet to exist read
  • header: a logical value. If True, the kickoff row is used as cavalcade names.

Instance of usage:

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

Summary


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

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

Infos

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


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

Prove me some beloved with the like buttons below... Cheers 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 à united nations ami ou en le partageant sur Twitter, Facebook ou Linked In.

Montrez-moi un 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!