Import Multiple Sheets into Multiple Data Frames in R

Question

I have an Excel file with a lot of sheets and I need a code to import each sheet in a separate data frame which will be named in the same convention as the sheet name in Excel.

Example, tabs A, B, C will be imported as data frame A, B, and C respectively.

From other threads, I saw codes like: length(excel_sheets(filename)) to get the number of sheets in the file

Then create a list that would contain each tab:

read_excel_allsheets <- function(filename) {
  sheets <- readxl::excel_sheets(filename)
  x <-    lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  names(x) <- sheets
  x
}

But I do not know how the tabs gets imported into R from there.

Would greatly appreciate the help. Thanks in advance!

Answer 1

Here's one way to do it:

# write test data
tf <- writexl::write_xlsx(
  list("the mtcars" = mtcars, "iris data" = iris), 
  tempfile(fileext = ".xlsx")
)

# read excel sheets
sheets <- readxl::excel_sheets(tf)
lst <- lapply(sheets, function(sheet) 
  readxl::read_excel(tf, sheet = sheet)
)
names(lst) <- sheets

# shove them into global environment
list2env(lst, envir = .GlobalEnv)

Answer 2

Your function reads in all the tabs and saves them as elements of a single list (because of lapply()). You can take the elements out of the list with list2env:

your_excel_list <- read_excel_allsheets("test.xlsx")
list2env(your_excel_list, .GlobalEnv)

You'll see that the named elements of your list are now data frames (or actually tbl_df) in your global environment