Merging Excel files in a single workbook with same column headers

[kuttan98]

Hi,
I have huge excel workbook with me. The workbook has almost 100 sheets. Each sheet has data of a particular school. All the sheets in the workbook have same column names. I need to merge all the 100 sheets and make a single sheet in a workbook so that I can get all the data consolidated in one sheet.
Is this possible to do?

[JackDavison]

It certainly is!

Your approach is going to look something like this:

library(readxl)
library(purrr)
library(dplyr)

your_path <- "C:/..."

sheets <- excel_sheets(your_path)

map_dfr(.x = sheets, .f = ~read_excel(your_path, sheet = .x) %>% mutate(sheet = .x))

First you define the path to your file.

Then detect all of the sheet names using excel_sheets().

Then finally you're going to "map" over all of the sheet names with the read_excel() function. I've also added a mutate() step to be able to identify which row of your final data comes from which sheet.