How to read a range in read_excel within a loop (where length(x) == 1L)-CodePudding

I'm reading multiple Excel files and sheets within those files in loops. A certain range should be read from those sheets and added to a dataframe, corresponding to each file.

With the code I have written so far I can read the files and sheets and put them into a dataframe. However, it gives me the following error when specifying the range:

Error in as.cell_limits.character(range) : length(x) == 1L is not TRUE

path <- "my file path"
files_list <- list.files(path, pattern="*.xlsx", full.names = TRUE)
files_list_names <- str_extract(list.files(path, pattern="*.xlsx"),"[^.] ") ###extract filename without file extension

count_files <- length(files_list_names)

for (i in 1:count_files){

  current_file <- files_list_names[i]

current_file_data <- read_excel(files_list[i], range="B22:B30")

  this_file_sheets <- excel_sheets(files_list[i])
  count_sheets <- length(this_file_sheets)
  
    for (j in 1:count_sheets){
      current_sheet_data <- read_excel(files_list[i],sheet = this_file_sheets[j],range("F22:F30"))
      bind_cols(current_file_data,current_sheet_data)
    
  }

  assign(paste0(current_file),current_file_data,envir = .GlobalEnv)
  
}

I have absolutely no clue what that error means and I can't find anything on the web.

As always, your help is much appreciated!

CodePudding user response：

Be sure to use range=, rather than range() when you call readxl::read_excel(). The latter will be invoking base::range() function. When you pass a string to base::range(), you get a vector of length 2 like this:

base::range("B22:B30")

[1] "B22:B30" "B22:B30"

If that vector of length 2 is passed to the range parameter of the read_excel() function, you will get the above error.