0

I am trying to split excel file with multiple sheets to a new file based on prefix in sheet name.

I would like to output all sheets with prefix a_ to a.xslx , b_ to b.xslx and so on

Here is the code so far:

library(readxl)
library(readr)
library(writexl)

#get sheet names
sheets <- excel_sheets("/path/to/excel/file")
filenames <- paste0(sheets, ".xslx")

#sheet prefix
m <- regexpr("^.*_", filenames) 
file_prefix <- unique(regmatches(filenames, m))
prefnames <- paste0(file_prefix, ".xslx")

#read_excel - sheets with same prefix 
datspref <- lapply(file_prefix, read_excel, path = "/path/to/excel/file")

#save sheets with same prefix to a new excel file
lapply(seq_along(dats), function(i) write_xlsx(dats[[i]], prefnames[i]))

Appreciate any help!

Rdu U
  • 57
  • 4
  • sorry, yes it is a typo – Rdu U Mar 18 '22 at 18:21
  • You say you want sheets *"with prefix a_ to a.xlsx"*, but your pattern is changing (say) `a_123` to `a_`, perhaps you'd do better with `regexpr("^[^_]+(?=_)", filenames, perl=TRUE)`? – r2evans Mar 18 '22 at 19:17
  • Excel sheets are named as a_1, a_2,a_3, b_1,b_2,c_1,C_3 and so on.. I want to output sheets a_1,a_2 and a_3 as a.xlsx; and b_1, b_2 sheets as b.xlsx – Rdu U Mar 18 '22 at 19:29
  • I tried changing regexpr but how do I read multiple sheets with that prefix – Rdu U Mar 18 '22 at 19:31

1 Answers1

1

Setup: I made sample.xlsx with the following sheets (and the same 2x2 table on each sheet):

enter image description here

xlsxfile <- "sample.xlsx"
sheetnames <- readxl::excel_sheets(xlsxfile)
sheetnames
# [1] "a_1" "a_2" "a_3" "b_1" "b_2" "c_1" "c_3"
newxlsxfiles <- sub("_.*", "", sheetnames)
newxlsxfiles
# [1] "a" "a" "a" "b" "b" "c" "c"
dats <- lapply(setNames(nm = sheetnames), readxl::read_xlsx, path = xlsxfile)
str(dats)
# List of 7
#  $ a_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ a_2: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ a_3: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ b_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ b_2: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ c_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
#  $ c_3: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   ..$ a: num 1
#   ..$ b: num 2
datspl <- split(dats, newxlsxfiles)
str(datspl)
# List of 3
#  $ a:List of 3
#   ..$ a_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#   ..$ a_2: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#   ..$ a_3: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#  $ b:List of 2
#   ..$ b_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#   ..$ b_2: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#  $ c:List of 2
#   ..$ c_1: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2
#   ..$ c_3: tibble [1 x 2] (S3: tbl_df/tbl/data.frame)
#   .. ..$ a: num 1
#   .. ..$ b: num 2

invisible( Map(writexl::write_xlsx, datspl, paste0(names(datspl), ".xlsx")) )

list.files(pattern = "xlsx$")
# [1] "a.xlsx" "b.xlsx" "c.xlsx"
readxl::excel_sheets("a.xlsx")
# [1] "a_1" "a_2" "a_3"

FYI: if you need to remove the leading a_ from the sheet names, just change the name of dats before splitting it, perhaps

# ...
names(dats) <- sub("^[^_]*_", "", sheetnames)
datspl <- split(dats, newxlsxfiles)
# ...
r2evans
  • 141,215
  • 6
  • 77
  • 149