CODEWITHSUNDEEP
rexcelloopsopenxlsxreadxl
h09812349
h09812349

Reputation: 139

Stuck on openxlsx error in function to import multiple excel files into R

I am attempting to write a function to read in multiple excel files into R, however I am trying to use openxlsx specifically because later on in my program I want to output specific columns/rows into an excel file without disturbing the existing excel formulas in the other columns of the file. The function I have written is below, but I am not sure why I get the error Error in file(description = xlsxFile) : invalid 'description' argument when I run it.

library(openxlsx)

file.list <- list.files(pattern='*.xlsx')
sheet <- "Sheet 1"

# Start loop
for (i in seq(length(file.list))) {
  
wb <- loadWorkbook(file = i)
  

#Read a section of a sheet 
Data_Info<- read.xlsx(wb, sheet = sheet,
                                colNames = TRUE, #Column Names are there
                                rows = seq(1:nrow(df)), #Copy out these rows
                                cols = seq(1:15)) #Copy out these columns

# Assign a name to the data frames created
assign(paste("Data_00", i), Data_Info)
}

traceback()

3: file(description = xlsxFile)
2: getFile(file)
1: openxlsx::loadWorkbook(file = i)

Update: thank you to the comments. It looks like the issue was as I_O mentioned in the comments, that the for (i in seq(length(file.list))) part of the code means it is looping through integers and not file names. After changing that to for (i in file.list), the loop is working.

Upvotes: 0

Views: 56

Answers (1)

Friede
Friede

Reputation: 8230

You can re-shape your code quite a bit:

# df needs to be defined, since you are using nrow(df)
file_list=list.files(pattern="*.xlsx")
data_list = lapply(file_list, \(i) read.xlsx(
  loadWorkbook(file=i), sheet="Sheet 1", 
  colNames=TRUE, rows=seq(nrow(df)), cols = seq(15L)) }) |> 
  setNames(paste0("Data_00", seq_along(file_list)))

This, for example, runs paste0() once and not in each iteration.

Upvotes: 1

Related Questions