read excel spreadsheet with many sheets --> unique data frames

[help]

hi is there a way to read in the tabs of an excel spreadsheet that contain a certain word(animal)? each tab should show up as an individual data frame? thank you!

for example, if an excel spreadsheet has 50 tabs, I want to extract the ones that have the word "animal" in them. in my environment, there should be a DataFrame for each tab with the word animal. the DataFrame name is the same name as the tab

[scottyd22]

Below is one approach to extract all worksheets containing the word "animal" and assign them to your global environment. Set Path to the file path of your .xlsx file. Read in all of the sheets in the file using the excel_sheets() function, keeping only those with the word "animal". The get_df() function reads in the data frame, sets the data frame name to the sheet name (all lowercase) with underscores replacing spaces, and then assigns it to the global environment. Finally, use walk() to step through this for each sheet.

library(readxl)
library(tidyverse)

Path = 'path to your .xlsx file'

sheets = excel_sheets(path = Path)
sheets = sheets[str_detect(sheets, 'animal')]


get_df = function(i) {
  df = read_xlsx(path = Path, sheet = i)
  descr = str_to_lower(i)
  descr = str_replace_all(descr, ' ', '_')
  
  assign(descr, df, globalenv())
}

walk(sheets, get_df)