Export excel to individual workbooks

[Slavek]

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?

[nirgrahamuk]

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")))

[Slavek]

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

[nirgrahamuk]

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

[Jo1984]

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