How to create data frames from multiple sheets of .xlsx file using for loop

Question

I'm new to R programming. For now, I have an Excel file (.xlsx) with 3 sheets inside. I would like to create 3 data frames, one for each sheet from the Excel file using a for loop.

I used library(readxl) and read_excel() to read the file

First, I created the list named result and imported the data from 3 sheets into the list using the code below

library(readxl)
result <- list()

for (i in 1:3) {
  result[[i]] <- read_excel("students.xlsx", sheet = i)
} 

Next, I would like to create 3 data frames from the list and name the data frames with the sheet's names but I can't figure out what to do next to make the code work.

What can I try next?

Answer 1

Please alternatively try the below code

# get the names of the excel sheets
name_excel <- readxl::excel_sheets('C:\\Users\\Documents\\example.xlsx')

# custom function to read the data from the individual sheet of the excel file
read_excel <- function(x){
  excel <- readxl::read_xlsx('C:\\Users\\Documents\\example.xlsx', sheet = x)
  assign(x,excel, envir = globalenv())
}

# generate separate data frames of each individual sheet
purrr::map(name_excel, read_excel)

^{Created on 2023-11-09 with reprex v2.0.2}