How to import multiple excel sheets with tabs named in numeric only in R-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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"