CODEWITHSUNDEEP
rtidyverse
Shlammed
Shlammed

Reputation: 21

Importing a list of excel files in R by sheet names containing a specific string

I have 36 excel files and need to read only one sheet from each of them into a dataframe. The sheet names are not all the same but they share a common string (e.g., "Expense Audit", "Jan Expense Audit", "19 Expense Audit"). I would like to write a function to list.files and then use read_excel to pull only the sheets containing the "Expense Audit" string into a single dataframe.

Upvotes: 1

Views: 2061

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389175

You can try :

library(purrr)
library(readxl)

#List all the excel files
file_path <- list.files(path = '/path/to/excel/files/', pattern = '\\.xlsx$', full.names = TRUE)

#Read each excel file and combine them in one dataframe
map_df(file_path, ~{
  #get all the names of the sheet
  sheets <- excel_sheets(.x)
  #Select the one which has 'Expense Audit' in them
  sheet_name <- grep('Expense Audit', sheets, value = TRUE)
  #Read the excel with the sheet name
  read_excel(.x, sheet_name)
}) -> data

data

Upvotes: 7

Related Questions