Read multiple “.xlsx” files

Question

I am trying to read multiple excel files under different folders by R

Here is my solution:

    setwd("D:/data")
    filename <- list.files(getwd(),full.names = TRUE) 
# Four folders "epdata1" "epdata2" "epdata3" "epdata4" were inside the folder "data" 
    dataname <- list.files(filename,pattern="*.xlsx$",full.names = TRUE)
# Every folder in the folder "data" contains five excel files
    datalist <- lapply(dataname,read_xlsx)

    Error: `path` does not exist:'D:/data/epidata1/出院舱随访1.xlsx'

But read_xlsx was successfully run

   read_xlsx("D:/data/epidata1/出院舱随访1.xlsx")

All file directories are available in the "data" folder and why R fails to read those excel file?

Your help will much appreciated!

`*.xlsx$` is not a valid regex.. can you try `.*\\.xlsx$` – Wimpel Jul 04 '22 at 13:46 — Wimpel, Jul 04 '22 at 13:46

score 0 · Answer 1 · answered Jul 04 '22 at 16:30

I dont see any point why your code shouldnt work. Make sure your folder names are correct. In your comments you write "epdata1" and your error says "epidata1". I tried it with some csv and mixed xlsx files.

This is again what i would come up with, to find the error/typo:

library(readxl)

pp <- function(...){print(paste(...))}


main <- function(){
  # finding / setting up data main folder
  # You may change this to your needs
main_dir <- paste0(getwd(),"/data/")

pp("working directory:",dir_data)
pp("Found following folders:")
pp(list.files(main_dir,full.names = FALSE))
data_folders <- list.files(dir_data,full.names = TRUE)

pp("Found these files in folders:",list.files(data_folders,full.names = TRUE))
pp("Filtering *.xlsx files",list.files(data_folders,pattern="*.xlsx$",full.names = TRUE))
files <- list.files(data_folders,pattern="\\.xlsx$",full.names = TRUE)

datalist <- lapply(files,read_xlsx)

print(datalist)
}

main()

Read multiple “.xlsx” files

1 Answers1