[R] readxl::excel_sheets in tryCatch() doesn't catch error

[Phillip-Jan van Zyl]

Hi R programmers

I am reading multiple .xls and .xlsx files from a directory using readxl from tidyverse. When reading fails, the code should continue on to the next file.

However, when I call the custom function readExcelSheets (in a loop and with the tryCatch function) I get an error for some files and the code then stops executing. How can I force my code to continue on to the next files?

Here is the function:

readExcelSheets <- function(curPath) {
  out <- tryCatch(
    {
      message("This is the 'try' part")
      dat <- excel_sheets(curPath)
    },
    error=function(cond) {
      message(paste("Error in opening Excel file with readxl read sheets:", curPath))
      message("Here's the original error message:")
      message(cond)
    },
    warning=function(cond) {
      message(paste("readxl caused a warning en reading sheets:", curPath))
      message("Here's the original warning message:")
      message(cond)
    },
    finally={
      message(paste("Processed file for sheets:", curPath))
      message("End of processing file for sheets.")
    }
  )
  return(out)
}

The loop looks like this:

listLength <- length(excelList)
for (excel_file in excelList) {
  curPath <- excel_file
  sheetNames <- NULL
  sheetNames <- withTimeout({readExcelSheets(curPath)}, timeout = 5, onTimeout="silent")
  if(is.null(sheetNames)){next}
  for (sheetName in sheetNames){
    # do something
  }
}

The problem is that I get an error:

Error: Evaluation error: zip file '<the path to the file>' cannot be opened.

And then execution of the loop stops without progressing to the next Excel file. Note that for the first n=+-20 files the code works as expected. I think that there may be an error in the full path name (such as a text encoding error), but my point is that it should exit silently and progress to the next Excel file even if the path is not found.

Best regards
Phillip
	[[alternative HTML version deleted]]