0

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

nak5120
  • 4,089
  • 4
  • 35
  • 94

1 Answers1

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

DJV
  • 4,743
  • 3
  • 19
  • 34
  • Thank you! Is there a way to have them as separate dataframes instead of all in 1? I would like each sheet name to be it's own dataframe since each sheet is in a different format. So the expected result would be 10 different dataframes representing each sheet rbinded with all the files in that folder. – nak5120 Feb 25 '19 at 15:29
  • Figured out a workaround with your code as a base. Thanks for the help! – nak5120 Feb 25 '19 at 16:39
  • I apologize, just seen your comment. You're welcome! :) – DJV Feb 25 '19 at 20:54