1

I have an excel workbook with 100s of sheets with tab named as strings (sheet1, R Import, etc.) and numeric (123, 456, etc.). But I want to import all the sheets for which the tab names are in numeric only. I have the following code to import all sheets but not sure how to import just the sheets with numeric tab names only:

read_excel_allsheets <- function(filename) { 
  sheets <- readxl::excel_sheets(filename) 
  x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X)) 
  names(x) <- sheets 
  x 
}

I want to perform this operation in R. Any help would be much appreciated. Thanks!

oguz ismail
  • 1
  • 16
  • 47
  • 69
aseb
  • 27
  • 3

3 Answers3

0

Though there ma be a more elegant solution, you could try this, which will create an object based on the sheet name of only the numeric sheets:

library(readxl)
flpth <- "your_filepath/Book1.xlsx"
sheetz <- excel_sheets(path = flpth)

for(xx in sheetz[!grepl("\\D", sheetz)]){
  assign(xx, read_excel(flpth, sheet = xx))
}
jpsmith
  • 11,023
  • 5
  • 15
  • 36
0

to get only numeric sheets or just which contain numeric you can use this:

sheets <- c("11", "sheet", "sheet2", "22")
# ------only numeric sheets
sheets1 <- sheets[!is.na(as.numeric(sheets))]
sheets1
# > sheets1
# [1] "11" "22"
# ------sheets which contain numeric
sheets2 <- sheets[grepl("[0-9]", sheets)]
sheets2 
#> sheets2
#[1] "11"     "sheet2" "22"
utubun
  • 4,400
  • 1
  • 14
  • 17
islem
  • 236
  • 1
  • 6
0

I would do it this way.

sheets <- readxl::excel_sheets('file.xlsx')

xlist <- lapply(
    grep('^\\d+$', sheets, value = TRUE),
    readxl::read_xlsx,
    path = 'file.xlsx'
)
utubun
  • 4,400
  • 1
  • 14
  • 17