Reading multiple Excel files and merge them sheet-wise

Question

I have about 50 Excel documents. They contain the ensuing sheets (Sheet1, Sheet2, Sheet3, and Sheet4). Additionally, each sheet's columns are the same across all 50 files. I must open each spreadsheet file and combine the data from the four sheets into four separate dataframes and excel files.

To read the 50 files and store the workbook as a temporary variable, I attempted to include a for loop I'm having trouble reading each page from them and binding it to a final variable, though.

Answer 1

This will analyse each Excel and combine its sheets into a single DF. A list of dataframes will be returned by the function. After the function returns the list, the dataframes are bound together.

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

I hope this helps you.