Writing for loop to read excel sheets

Question

I need to read multiple sheets from excel into R (into separate dataframes). I could not figure out how to write a for loop so I just used brute force.

Here is my code

mydata1 <- read_excel("exceldata.xlsx", sheet = 1)
mydata2 <- read_excel("exceldata.xlsx", sheet = 2)
mydata3 <- read_excel("exceldata.xlsx", sheet = 3)
mydata4 <- read_excel("exceldata.xlsx", sheet = 4)
mydata5 <- read_excel("exceldata.xlsx", sheet = 5)
mydata6 <- read_excel("exceldata.xlsx", sheet = 6)
mydata7 <- read_excel("exceldata.xlsx", sheet = 7)
mydata8 <- read_excel("exceldata.xlsx", sheet = 8)
mydata9 <- read_excel("exceldata.xlsx", sheet = 9)

This works but I was hoping someone could show me how to use a for loop or lapply for this instead. Also do I need to upload the data file? Im new to this website.

Thank you.

`mydata <- lapply(1:9, read_excel, path="exceldata.xlsx")` will read the sheets into a list. If you want the elements named, then `mydata <- sapply(sheet_names(path), read_excel, path=path, simplify = FALSE)`. — r2evans, Mar 22 '21 at 15:47

score 3 · Answer 1 · answered Mar 22 '21 at 15:45

We can use loop on the sheet number index and read those into a list

mylist <- lapply(1:9, function(i) read_excel("excel_data.xlsx", sheet = i))

It is better to keep it in a list instead of creating multiple objects in the global env. The list can also be named

names(mylist) <- paste0('mydata', seq_along(mylist))

and the elements can be extracted with [[ or $

mylist[["mydata1"]]
mylist$mydata2

With for loop, a list can be initialized first

mylist2 <- vector('list', 9)
for(i in seq_along(mylist2)) {
    mylist2[[i]] <- read_excel("excel_data.xlsx", sheet = i)
 }

Also, if you have many excel files and they don't always have the same sheets, `readxl::excel_sheets()` will return a character vector with the names of the sheets. So instead of `lapply(1:9, ...)` you could use something more generic, such as `lapply(excel_sheets("excel_data.xlsx"), ...)` — Thiago Jacomasso, Mar 22 '21 at 16:16

gabt · Answer 2 · 2021-03-22T16:50:12.193

There are many answers to this question out there.

Anyway, a simple solution for someone who's new to R may be:

# load library
library("openxlsx")

# set path to the directory containing the files
myDir <- "path/to/files/"

# read all the files in the directory
fileNames <- list.files(myDir)

# declare empty list
allFiles <- list()

# set counting index
i <- 1

# loop through the files in myDir
for (fileN in fileNames) {
    # read the file and store in position i
    allFiles[[fileN]] <- read.xlsx(paste(myDir, fileN, sep=""))
    # go to next position by updating the counting index
    i <- i + 1
}
# at this point you can access your files by, for instance:
allFiles[[1]]
# where 1 is the first file in your file list

or, if you don't want to use the counting index:

# load library
library("openxlsx")

# set path to the directory containing the files
myDir <- "path/to/files/"

# read all the files in the directory
fileNames <- list.files(myDir)

# declare empty list
allFiles <- list()

# loop through the files in myDir
for (fileN in fileNames) {
    # read the file and store it into the list
    allFiles[[fileN]] <- read.xlsx(paste(myDir, fileN, sep=""))
}
# at this point you can access your files by, for instance:
allFiles[[fileN]]
# where fileN is the variable you just used in the for loop
# or you can access them using the actual file names but with quotes
allFiles[["actual_file_name"]]

Writing for loop to read excel sheets

2 Answers2