0

a very novice R user here trying to compile multiple outputs into a single file. I have seen this thread Combining multiple worksheets from multiple excel workbooks into a single workbook via. R, but it doesn't quite answer what I'm trying to do (or I don't understand it well enough to figure it out from there).

I have some running performance data, collected from monitoring up to 16 people at once. This results in an excel workbook where I have individual tabs for things like maximum speed, average speed, distance covered etc. I have then collected this data at multiple times - so I currently have 4 workbooks, each with 10 worksheets. I want to combine these into a single master workbook with these same 10 worksheets.

I have worked out a way to do this per individual sheet using the following (very basic) code:

AvSpdJan<-read_excel("Jan.xlsx", 
                sheet = "AvSpd")

AvSpdFeb<-read_excel("Feb.xlsx", 
                sheet = "AvSpd")

AvSpdMar<-read_excel("Mar.xlsx", 
                sheet = "AvSpd")

AvSpdApr<-read_excel("Apr.xlsx", 
                 sheet = "AvSpd")

CompiledAvSpd <- rbind(AvSpdJan, AvSpdFeb, AvSpdMar, AvSpdApr)

view(CompiledAvSpd)

write.xlsx(CompiledAvSpd, file = "CompiledAvSpd.xlsx",overwrite = TRUE)

This code works fine, but, I have three 'problems':

  1. This processes only the "AvSpd" sheet - I have to repeat it for the other 9 variables
  2. This outputs each variable as it's own summary, not as a single workbook with all variables
  3. I will be collecting more data and adding it monthly (hence the overwrite command in the write.xlsx line).

None of these are major issues, I can continue with what I have and just write in a new line each month, and find/replace for the other variables. But, I feel like there is likely an easy way to solve all of these problems with a single code - I just can't figure out what that is...

ExSciGuy
  • 1
  • You need to use a loop. Here is one possible solution https://stackoverflow.com/questions/58040908/how-to-combine-multiple-excel-files-having-multiple-worksheets-into-a-single-exc. There are others if you perform a search on this site – Dave2e May 12 '22 at 01:05
  • Thanks @Dave2e, I'll have a look at loops and see if I can figure it out. – ExSciGuy May 12 '22 at 02:22

0 Answers0