Export excel to individual workbooks

Hi,
Thank to nirgrahamuk I managed to export individual data frames to separate tabs of one Excel workbook:

source <- data.frame(
   stringsAsFactors = FALSE,
                              URN = c("21GB01293040","21GB01240221",
                                      "21GB03294610","21GB01309069","21GB03078286",
                                      "21GB01086060","21GB01169525","21GB01209144",
                                      "21GB01204925","21GB90063865",
                                      "21GB01068838","21GB01176411","21GB01215531"),
   Q5_1 = c(0, 0, 0, 0, 0, 1, 1, 1, 0, 1, 0, 0, 1),
   Q5_2 = c(0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0),
   Q5_Top2 = c(0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0),
   Q5_ThreeLevels = c(1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0),
               QA31 = c(7, 1, 8, 3, 8, 8, 8, 6, 2, 2, 7, 8, 8),
  Qtr = c(1, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2)
              )

gsrc <- group_by(df,
          Country,
          Type)

keyvar <- gsrc %>% 
  group_keys() %>% 
  mutate(key=paste(Country, Type)) %>% 
  pull(key)

gsrc %>% 
group_split()%>%
 as.list() %>% 
setNames(keyvar)%>% 
write.xlsx(file="out2.xlsx")

Using large data, we might have 'heavy' data frames so exporting them to one workbook might be problematic. Do you know how I could export them to separate Excel workbooks instead?

I think your source data for this question has been mixed up from another recent question.
Anyway, you could approach it in the following way. using iwalk from purrr to iterate an action with sideefffects (writing to disk) over a named list.

library(tidyverse)
library(openxlsx)

df <- data.frame(
  stringsAsFactors = FALSE,
  URN = c("aaa","bbb","ccc","ddd",
          "eee","fff","ggg","hhh","iii","jjj","kkk","lll",
          "mmm"),
  Country = c("xxx","xxx","yyy","yyy",
              "yyy","zzzz","zzzz","yyy","zzzz","xxx","zzzz","yyy",
              "zzzz"),
  Type = c("Sales","Service","Sales",
           "Sales","Service","Service","Sales","Sales","Service",
           "Sales","Service","Service","Sales")
)

gsrc <- group_by(df,
                 Country,
                 Type)

keyvar <- gsrc %>% 
  group_keys() %>% 
  mutate(key=paste(Country, Type)) %>% 
  pull(key)

book_contents <- gsrc %>% 
  group_split()%>%
  as.list() %>% 
  setNames(keyvar)

iwalk(book_contents,~
        write.xlsx(.x,paste0(.y,".xlsx")))

Thank you. I used the path:

iwalk(book_contents,~
        write.xlsx(.x,paste0(.y,"C:/Users/xxx/OneDrive/_Useful/R templates/.xlsx")))

No error but no fileseither

iwalk(book_contents,~
        write.xlsx(.x,paste0("C:/Users/xxx/OneDrive/_Useful/R templates/",.y,".xlsx")))
2 Likes

write.csv2(name of the file,file='name save.ccv',sep=';')

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.