How can I read multiple excel files from multiple folders in R Programming

Question

This is my code:

library(readxl)
file.list <- list.files(pattern='subject*.xlsx')
df.list <- lapply(file.list, read_excel)

My folder structure is like:

C:\Users\sangu\Documents\R\Quality Control\Methodist microsurgery with output\subject01    
C:\Users\sangu\Documents\R\Quality Control\Methodist microsurgery with output\subject02    
C:\Users\sangu\Documents\R\Quality Control\Methodist microsurgery with output\subject03

Inside each of this folder Excel file name will be in this structure:

Subject01_tai.xlsx   
Subject02_tai.xlsx        
Subject03_tai.xlsx

and I need to read a column from 3 excel files.

Answer 1

If there is only one excel file per folder and your original file.list is the list of folders, then this should work:

for(i in 1:length(file.list)){
  setwd(file.list[i])
  temp_file <- list.files()
  eval(parse(text=paste0("file_",i," <- read_excel(temp_file)")))
}

That will read in each file and save it to a variable named file_1, file_2, etc.

Answer 2

Try something like this:

library(openxlsx)

path.to.sub.folders <- "C:/Users/sangu/Documents/R/Quality Control/Methodist microsurgery with output/"
folders.to.read <- paste0(path.to.sub.folders, "subject0", 1:3)
files.to.read <- paste0("Subject0", 1:3, "_tai.xlsx")

### Alternative ###
folders.to.read <- list.dirs(path.to.sub.folders)
#### ---- ####

for(k in 1:length(files.to.read)){
tmp.file.name <- paste0(folders.to.read[k], "/", files.to.read[k])
tmp.df <- read.xlsx(tmp.file.name, sheet = 1)
assign(files.to.read[k], tmp.df)
rm(k, tmp.df, tmp.file.name)
}