read.xlsx2 | Skipping if sheetName does not exist

Question

I am trying to read multiple excel files in a folder using the read.xlsx2 function. I only need to read a particular sheet titled 'Returns' or 'Prices'.

Is there a way I can give an 'OR' argument in the function and also skip a file if it contains neither of the sheets?

P.s.: Each file will have either a 'Returns' or a 'Prices' sheet or neither but not both so there cannot be a clash.

Thanks

Ronak Shah · Accepted Answer · 2020-09-24 07:43:27Z

2

You could read all the sheet names of the file and using intersect select one of 'Returns' or 'Prices' whichever is present in the sheet and read the excel file with that sheet.

Using readxl you can do this as :

library(readxl)

all_files <- list.files(pattern = '\\.xlsx$')

result <- lapply(all_files, function(x) {
  all_sheets <- excel_sheets(x)  
  correct_sheet <- intersect(all_sheets, c('Returns', 'Prices'))
  if(length(correct_sheet)) read_xlsx(x, correct_sheet)
})

result will have a list of dataframes. If you want to combine the data into one dataframe and if they have same column names you can use do.call(rbind, result)

answered Sep 24, 2020 at 7:43

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

read.xlsx2 | Skipping if sheetName does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related