Search code examples
rfor-loopxlconnect

XLConnect - readWorksheet with looping object

I am using R Studio version 3.1.2 with XLConnect package to load, read and write multiple xlsx files. I can do this with duplicating and creating multiple objects but I am trying to do it using 1 object(all files in the same folder). please see examples I can do this listing each file but want to do it using a loop

tstA <- loadWorkbook("\\\\FS01\\DEPARTMENTFOLDERS$\\tst\\2015\\Apr\\DeptA.xlsx")
tstB <- loadWorkbook("\\\\FS01\\DEPARTMENTFOLDERS$\\tst\\2015\\Apr\\DeptB.xlsx")

This is the way im trying to do it but get an error 

dept <- c("DeptA","DeptB","DeptC")
for(dp in 1:length(dept)){
dept[dp] <- loadWorkbook("\\\\FS01\\DEPARTMENTFOLDERS$\\tst\\2015\\Apr\\",dept[dp],".xlsx")}

After this I want to use the readWorksheet function from XLConnect.

Apologies for the lame question but I am struggling to workout how best to do this. Thanks

Solution

  • You can read all the files into a list in one operation as follows (adjust pattern and sheet as needed to get the files/sheets you want):

    path = "\\\\FS01\\DEPARTMENTFOLDERS$\\tst\\2015\\Apr\\"

df.list = lapply(list.files(path, pattern="xlsx$"), function(i) {
  readWorksheetFromFile(paste0(path, i), sheet="YourSheetName")
})

    If you want to combine all of the data frames into a single data frame, you can do this:

    df = do.call(rbind, df.list)