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!
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.