1

I have multiple data frames that I would like to export into different tabs of an excel/csv file. I'll be grouping my 15 data frames into three groups of five. This way I would have three excel sheets with five different tabs instead of 15 individual excel sheet.

To export to excel:

#fake data 
data_1<-data.frame(c(03,23,4,2))
data_2<-data.frame(c(0223,3,1,2))
data_3<-data.frame(c(0232,3,1,1))
data_4<-data.frame(c(21,23,5,6))
data_5<-data.frame(c(24,5,6,7))

#fake names
mydatasets<-c(data_1,data_2,data_3,data_4,data_5)
mytitles<-c("data1", "data2", "data3","data4", "data5")


#for loop to write out individual csv files
for (i in 1:5)) {
a <- mydatasets[i]
title<-mytitles[i]
myfile <- paste0(title, "_", ".csv")
write.csv(a, file = myfile)
               }

How do I get the above code to merge those csv files into multiple tabs of one csv file or excel file?

Meli
  • 345
  • 5
  • 15

1 Answers1

4

CSV files consist of only 1 sheet. An alternative would be to write to XLSX. The function xlsx::write.xlsx takes an argument sheetName:

library(xlsx)

# Use data from the question
for (i in seq_along(mydatasets)) {
  write.xlsx(x = mydatasets[i], 
             file = "myfile.xlsx", 
             sheetName = mytitles[i],
             append = TRUE)
}

Note append = TRUE to avoid overwriting the file instead of appending sheets to it.

The xlsx package depends on rJava. Using that package for the first time causes trouble sometimes – see this question for a common solution.

Community
  • 1
  • 1
CL.
  • 14,577
  • 5
  • 46
  • 73
  • The solution looks clean but when I try it on my end I have issues with package dependencies. I did `install.packages("xlsx") library(xlsx)` and then installed an rJava package because it was having a hard time loading the dependency on it's own and it still doesn't work. – Meli Mar 09 '16 at 17:31
  • Yeah, the `rJava` package can be a bit troublesome sometimes. What error message do you get? Does [this](http://stackoverflow.com/questions/9120270/how-can-i-install-rjava-for-use-with-64bit-r-on-a-64-bit-windows-computer) solve your problem? – CL. Mar 09 '16 at 17:35
  • That does solve my problem! Thanks. Now, I'm just trying to figure out how to maintain the formatting of each data frame. My output only gets me the first column of each data frame in a sheet. My real data sets are data frames with multiple columns. I didn't realize that when I call ` mydatasets<-c(data_1,data_2,data_3,data_4,data_5) ` that it turns it into a vector and not just a group. – Meli Mar 09 '16 at 19:49
  • You probably want to use list() instead of c(). Don't forget to change the subsetting appropriately: mydatasets[[i]] . – CL. Mar 09 '16 at 20:31