Exploratory Community

How to import all tabs from an excel doc into a single dataframe

#1

How to import all tabs from an excel doc into a single dataframe.

Suppose you have an Excel file and all the tabs in the Excel file are identically structured. To import all these Tabs from the Excel file into a single data frame, you can create a R Script Data Source Like Below.

Click Plus button next to the Data Frame and select “R Script”

On the R Script Data Frame Edtior, type in a script like below.

 filePath <- "~/Downloads/your_data.xlsx"
 sheets <- exploratory::get_excel_sheets(filePath)
 df <- lapply(sheets, function(sheet){
     exploratory::read_excel_file(filePath, sheet = sheet) %>% mutate(tab_name = sheet)
 })  %>%  exploratory::bind_rows()
 df

In this example, the Excel file as 3 Tabs (Tab1, Tab2, Tab3) and the sheet name is stored in tab_name column.