Frodo
0
I have close to 50 Excel files. Which have the following sheets (Sheet1, Sheet2, Sheet3 and Sheet4). And the columns in each sheets are identical across all the 50 files. I need to read each excel file and merge the 4 sheets data in 4 dataframes/4 excel files.
I tried to include for loop to read the 50 files and store the workbook as a temp variable. But to read each sheet from them and binding it to a final variable is where I'm stuck.
Okay with a VBA solution as well!
This will process each excel and merge the sheets for each excel to one df. The function will return a list of dataframes. The dataframes are binded together after the list is returned from the function.
library(readxl)
library(tidyverse)
files <- list.files(path="C:/data/55423/originals/r_test/",
pattern="*.xlsx",
full.names=TRUE)
allsheets <- function(filename) {
sheets <- readxl::excel_sheets(filename)
x <- lapply(sheets, function(x)
transform(readxl::read_excel(filename, sheet=x), sheetname=x))
return(x)
}
df <- lapply(files, allsheets)%>% bind_rows()