How to Extract Sheet Names from Excel File in R

How to extract sheet names from Excel file in R

You are looking for getSheets

Returns all worksheet names in a workbook.

R: reading multiple excel files, extract first sheet names, and create new column

If you happen to be working with only the first sheets of your files, then the following should help you grab the first sheets' names as the id for your dataframes:

attr(df.list, "names") <- sapply(file.list, function(x) excel_sheets(x)[1])

However, if you are considering importing the data from all the available sheets you will need to do a bit more work, starting with how you create your list of dataframes:

df.list <- lapply(file.list,function(x) {
  sheets <- excel_sheets(x)
  dfs <- lapply(sheets, function(y) {
    read_excel(x, sheet = y)
  })
  names(dfs) <- sheets
  dfs
})

This should create a list of lists, which should contain all the available data in your files. The lists inside the main list are appropriately named after the sheet names. So, you will not need to change any attributes afterwards. But to bind the dataframes together, you need to do:

rbindlist(lapply(df.list, rbindlist, id = "id"))

I hope this proves useful.

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

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

Read in Certain Sheets From Excel Workbook

You could use the readxl package like this and filter the Survey data sheets before reading everything in one go.

library(readxl)

# file including path if needed
file <- "Data.xls" 
# read the sheets and only keep the Survey sheets
sheets <- excel_sheets(file)
sheets <- sheets[grep("Survey", sheets)]

# read the data, only first 10 columns (A:J)
excel_data <- lapply(sheets, read_excel, path = file, range = cell_cols("A:J") )

You end up with a list of data. If all columns are identical you can use do.call("rbind", excel_data) or dplyr::bind_rows

Reading one sheet from excel in rstudio

Specify the sheet name from where you want to read the data in read_excel function -

dat <- read_excel("multi_anova.xlsx", sheet = "B")
dat$id

---

Related Topics