How to read a xlsx file with multiple Sheets in R?

by Tomas · Published 28 November, 2018 · Updated 11 May, 2021

Hi Everyone, today I want to show you how to read a xlxs file in R, for use this function you must have the library readxl, so the first step is install the readxl library.

# Yo can download the library from cran or github devtools::install_github('dragua/xlsx')
#install.packages('readxl')
library(readxl)

If we want to read a xlxs, we can use the function �read_excel�, for play with this function we need to pass two basic parameters basic: The file name and the Sheet that we want read.

#We can use this method to read for example the sheet 1.( with numbers)
dataWithNumericSheet <- read_excel("example.xlsx", sheet = 1)
dataWithNumericSheet

## # A tibble: 3 x 3
##      Id   Age Name     
##   <dbl> <dbl> <chr>    
## 1  1213    18 Peter    
## 2  1215    23 Joan     
## 3  1278    35 Magdalena

# Or we can use this another method with string
dataWithStringSheet <- read_excel("example.xlsx", sheet = "sheet 2")
dataWithStringSheet

## # A tibble: 3 x 2
##   Color Holidays
##   <chr> <chr>   
## 1 red   yes     
## 2 blue  no      
## 3 grey  yes

Sometimes we have more of one sheet in a Excel document, and we need all the data of document, in this case we can create a easy solution.

# Using excel_sheets, we can get the names of sheets and create a list with them.
sheets <- readxl::excel_sheets("example.xlsx")
sheets <- as.list(sheets)

# When we have the list, we createa loop for each sheet and we save the data in the variable "finalData".

finalData <- data.frame()
for (i in 1:length(sheets)){
  if (nrow(finalData) == 0 ) {
    data <- read_excel("example.xlsx", sheet = sheets[[i]] )
    finalData <- data
  }else{
    dataNext <- read_excel("example.xlsx", sheet = sheets[[i]] )
    finalData <-  cbind(finalData,  dataNext)
  }
}
finalData

##     Id Age      Name Color Holidays Country     City
## 1 1213  18     Peter   red      yes    EEUU New York
## 2 1215  23      Joan  blue       no   Spain   Madrid
## 3 1278  35 Magdalena  grey      yes  France    Paris

As you can see is very easy create a dataframe following our steps, if you have any problem to read excel documents write us and we will help you.