Read specifc cells in specific tab in multiple excel files using R-CodePudding

I have a folder containing several excel files(both xlsm and xlsx). I have to read specific cells on each file and have that as a single row and do that for all files. When I do it for 1 file I am able to do it using this code:

ClassID <- "test"
ClassDate <- read_excel("file1.xlsm", sheet = "SUMMARY", range = "E1:E1", col_names=FALSE)[[1]]
Curriculum <- read_excel("file1.xlsm", sheet = "SUMMARY", range = "C3:C3", col_names=FALSE)[[1]]
   
Summary <- cbind(ClassID,
                     ClassDate,
                     Curriculum)

But when I try to use the pattern function to read all files and use a for loop to run all files I get an error: Error in rbind2(argl[[i]], r) : cannot coerce type 'closure' to vector of type 'list' . When I look at the file.list object I can see that all my files are being retrieved.

file.list <- list.files(pattern = '*.xls')
df.list <- lapply(file.list, read_excel)

for (i in file.list){
ClassID <- "test"
ClassDate <- read_excel(i, sheet = "SUMMARY", range = "E1:E1", col_names=FALSE)[[1]]
Curriculum <- read_excel(i, sheet = "SUMMARY", range = "C3:C3", col_names=FALSE)[[1]]

SummaryNew <- cbind(ClassID,
                 ClassDate,
                 Curriculum)
Summary <- rbind(Summary,SummaryNew)
}

CodePudding user response：

This should work:

library(readxl)
file.list <- list.files(pattern = '*.xls')
res <- lapply(file.list, function(x) {
    ClassID <- "test"
    ClassDate <- read_excel(x, sheet = "SUMMARY", range = "E1:E1", col_names=FALSE)[[1]]
    Curriculum <- read_excel(x, sheet = "SUMMARY", range = "C3:C3", col_names=FALSE)[[1]]
    data.frame(ClassID, ClassDate, Curriculum)
})
do.call(rbind, res)

While you could in principle use cbind and rbind, that would create a matrix with all columns coerced to character. A data.frame can have columns with different classes, such as numeric, date, etc..

Here, we first create a list of data.frames and then use rbind on that to combine the list elements.