Using read_excel to upload and automatically name all existing workbook sheets (without specifying the number of sheets)

Question

I would like to adapt the answer below, taken from this question.

# Example data
write.xlsx(mtcars, "mt cars.xlsx")
write.xlsx(mtcars, "mt car s.xlsx")

temp = list.files(pattern="*.xlsx")

make_names <- function(x) {
  gsub("\\.", "_", make.names(gsub("*.xlsx$", "", x)))
}
names(temp) <- make_names(temp)

list2env(lapply(temp, read.xlsx), envir = .GlobalEnv)
#> <environment: R_GlobalEnv>

ls()
#> [1] "make_names" "mt_car_s"   "mt_cars"    "temp"

Let's assume that one of the Excel files has a second sheet (I tried to create a replicatable, but could not figure out how to write a second sheet with write.xlsx).

The code to load all Excel sheets from one Excel-file can be found here, (thanks to akrun). However in my case I am trying to upload a folder instead of a file.

How can I combine this code to do both of these things?

Is there an option to look for more sheets?

Answer 1

Something like this should work :

library(readxl)
paths <- list.files(pattern="*.xlsx")
read_all_sheets <- 
  function(path) sapply(excel_sheets(path), read_excel, path = path, USE.NAMES = TRUE, simplify = FALSE)
xl_list <- sapply(paths, read_all_sheets, USE.NAMES = TRUE, simplify = FALSE)

Answer 2

Does this do what you want? It extends to any number of excel files saved as *.xlsx and to any number of sheets contained within them.

library(tidyverse)

mtcars1 <- mtcars
mtcars2 <- mtcars

writexl::write_xlsx(list(mtcars1,mtcars2), "mtcars list.xlsx")
writexl::write_xlsx(mtcars1, "mtcars.xlsx")

List_of_sheets <- 
  # Pick all .xlsx files in working directory
  list.files(pattern="*.xlsx") %>% 
  # Loop over each file
  purrr::map(~{
    # Extract name of file and sheets within
    Excel_name = .x
    Sheets = readxl::excel_sheets(Excel_name)
    
    List = Sheets %>% 
      # For each sheet within a single .xlsx file - 
      purrr::map(~{
        # Add a column mentioning the excel file name and the sheet name
        readxl::read_excel(Excel_name,
                           sheet = .x) %>% 
          dplyr::mutate(`Excel file name` = Excel_name,
                        `Sheet name` = .x)
      })
  }) %>% 
  purrr::flatten()

names(List_of_sheets) <- List_of_sheets %>% 
  # Name the list components with the excel file name and the sheet name
  purrr::map_chr(~{
    .x %>% 
      dplyr::mutate(`Excel file and sheet name` = str_c(`Excel file name`,`Sheet name`,sep = " ")) %>% 
      dplyr::pull(`Excel file and sheet name`) %>%
      unique
  })

List_of_sheets %>% 
  # Result
  purrr::map(~{
    .x %>% 
      dplyr::slice(1:2)
  })

Output:

$`mtcars list.xlsx Sheet1`
# A tibble: 2 × 13
    mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb `Excel file name` `Sheet name`
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>             <chr>       
1    21     6   160   110   3.9  2.62  16.5     0     1     4     4 mtcars list.xlsx  Sheet1      
2    21     6   160   110   3.9  2.88  17.0     0     1     4     4 mtcars list.xlsx  Sheet1      

$`mtcars list.xlsx Sheet2`
# A tibble: 2 × 13
    mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb `Excel file name` `Sheet name`
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>             <chr>       
1    21     6   160   110   3.9  2.62  16.5     0     1     4     4 mtcars list.xlsx  Sheet2      
2    21     6   160   110   3.9  2.88  17.0     0     1     4     4 mtcars list.xlsx  Sheet2      

$`mtcars.xlsx Sheet1`
# A tibble: 2 × 13
    mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb `Excel file name` `Sheet name`
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>             <chr>       
1    21     6   160   110   3.9  2.62  16.5     0     1     4     4 mtcars.xlsx       Sheet1      
2    21     6   160   110   3.9  2.88  17.0     0     1     4     4 mtcars.xlsx       Sheet1