Read All Excel Files into R by Sheet with file nam

Question

I have a local folder with excel files in the same format. Each excel file has 10 sheets.

I want to be able to do the following:

1) Read all the excel files in R

2) Rbind all the results together but by sheet.

3) Result would be 10 new dataframes with all the excel files rbinded together.

4) New column will be added with file name

I have looked up code and the best I could find is this but it doesn't do it by sheet:

files = list.files()
library(plyr)
library(readr)
library(readxl)
data2=lapply(files, read_excel)
for (i in 1:length(data2)){data2[[i]]<-cbind(data2[[i]],files[i])}
all_data <- do.call("rbind.fill", data2) 

Has anyone had any success with this?

Thanks in advance

Answer 1

If you'd like you can also vectorize it using the tidyverse approach.

require(tidyverse)

df <- list.files(path = "your_path",
                       full.names = TRUE,
                       recursive = TRUE,
                       pattern = "*.xls") %>% 
tbl_df() %>%
mutate(sheetName = map(value, readxl::excel_sheets)) %>%
unnest(sheetName) %>% 
mutate(myFiles = purrr::map2(value, sheetName, function(x,y) {
    readxl::read_excel(x, sheet = paste(y))})) %>% 
unnest(myFiles)

*Somehow I was unable to flag it, so I'm copying my answer from here