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

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')

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)
## # 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")
## # 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
    dataNext <- read_excel("example.xlsx", sheet = sheets[[i]] )
    finalData <-  cbind(finalData,  dataNext)
##     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.

A hug

How to read a xlsx file with multiple Sheets in R?
Article Name
How to read a xlsx file with multiple Sheets in R?
Do you need know how to read a xlsx file in R? Don't worry, we can help you, follow our explanation and you will be able to do it easily.
Publisher Name
Publisher Logo

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *