I am trying to merge data from all sheets in all Excel files in a folder. All sheets and all files have the same headers and same data sets. I thought the code below would read all sheets, but it seems to be reading ONLY the first sheet in each file.
# This needs several other packages
# install.packages("XLConnect")
require(XLConnect)
setwd("C:/Users/Excel/Desktop/Coding/R Programming/Excel/Excel_Files/")
fpattern <- "File.*.xls*?" # pattern for filenames
output.file <- "Test.xls"
lfiles <- list.files(pattern = fpattern)
# Read data from all sheets
lfiles %>%
excel_sheets() %>%
set_names() %>%
map(read_excel, lfiles = lfiles)
This is an example using only R base functions and XLConnect:
library(XLConnect)
testDir <- "Excel_Files"
re_file <- ".+\\.xls.?"
testFiles <- list.files(testDir, re_file, full.names = TRUE)
# This function rbinds in a single dataframe
# the content of multiple sheets in the same workbook
# (assuming that all the sheets have the same column types)
rbindAllSheets <- function(file) {
wb <- loadWorkbook(file)
sheets <- getSheets(wb)
do.call(rbind,
lapply(sheets, function(sheet) {
readWorksheet(wb, sheet)
})
)
}
# Getting a single dataframe for all the Excel files
result <- do.call(rbind, lapply(testFiles, rbindAllSheets))