Reading multiple Excel files (with multiple sheets) in a directory in R

Question

I have multiple .xlsx files that I would like to read and combine into one file. However, each of the files contain two sheets so I would like to have one file that is all sheet 1 and then 1 file is all sheet 2.

I have used code like this before to read multiple files but it doesn't take into account different sheets.

files = list.files(path = "../input_data/", 
                         pattern = "*.xlsx", 
                         full.names = T)
  
combined_data = sapply(files, read_excel, simplify = F) %>% 
    rbind.fill()

I had tried adding the sheet parameter to the read_excel function but that didn't work. Any ideas? Thanks!

xwhitelight xwhitelight · Accepted Answer · 2020-09-14T14:30:51

I recommend using the openxlsx package which is the most modern:

df1 <- purrr::map_dfr(
  files,
  function(x) {
    openxlsx::read.xlsx(x, sheet = 1)
  }
)
df2 <- purrr::map_dfr(
  files,
  function(x) {
    openxlsx::read.xlsx(x, sheet = 2)
  }
)

Reading multiple Excel files (with multiple sheets) in a directory in R

1 Answers