1

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

New2Programming
  • 351
  • 1
  • 4
  • 17

1 Answers1

2

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)
eipi10
  • 91,525
  • 24
  • 209
  • 285
  • When I try and use this I get the following errors df.list = lapply(list.files(path), pattern="xlsx$"), function(i) { Error: unexpected ',' in "df.list = lapply(list.files(path), pattern="xlsx$")," > readWorksheetFromFile(paste0(path, i), sheet="TST") Error in paste0(path, i) : object 'i' not found > }) Error: unexpected '}' in "}" – New2Programming May 15 '15 at 15:53
  • Removed close parenthesis after `path`. Sorry about that. Try now. – eipi10 May 15 '15 at 16:07