CODEWITHSUNDEEP
rexceldataframereadxl
Muhammad Kamil
Muhammad Kamil

Reputation: 665

Writing for loop to read excel sheets

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.

Upvotes: 0

Views: 2838

Answers (2)

gbt
gbt

Reputation: 809

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"]]

Upvotes: 3

akrun
akrun

Reputation: 887881

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)
 }

Upvotes: 3

Related Questions