Read All Worksheets of Excel File into List in R (Example)
In this tutorial, I’ll illustrate how to import all sheets of an Excel file to a list in R.
The tutorial will consist of this:
Here’s how to do it…
Example Data & Packages
As the first step, we have to construct an example Excel file with multiple worksheets. For this, we have to install and load the xlsx package:
install.packages("xlsx") # Install xlsx package library("xlsx") # Load xlsx package |
install.packages("xlsx") # Install xlsx package library("xlsx") # Load xlsx package
Next, we can create an Excel file with three sheets using the following R syntax:
write.xlsx2(iris, "data_all.xlsx", row.names = FALSE) # Create Excel file with multiple sheets # [1] "Sheet1" "Sheet2" "Sheet3" write.xlsx2(mtcars, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet2") write.xlsx2(beaver1, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet3") |
write.xlsx2(iris, "data_all.xlsx", row.names = FALSE) # Create Excel file with multiple sheets # [1] "Sheet1" "Sheet2" "Sheet3" write.xlsx2(mtcars, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet2") write.xlsx2(beaver1, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet3")
Have a look at the previous screenshot. It shows that the example Excel file has three different worksheets.
Example: Importing All Sheets of Excel File Using readxl Package
The following R syntax explains how to import all sheets of our Excel file into a list in R.
First, we have to install and load the readxl package:
install.packages("readxl") # Install & load readxl package library("readxl") |
install.packages("readxl") # Install & load readxl package library("readxl")
Next, we can use the excel_sheets function to create a vector containing all sheet names as character strings:
sheet_names <- excel_sheets("data_all.xlsx") # Get sheet names sheet_names # Print sheet names # [1] "Sheet1" "Sheet2" "Sheet3" |
sheet_names <- excel_sheets("data_all.xlsx") # Get sheet names sheet_names # Print sheet names # [1] "Sheet1" "Sheet2" "Sheet3"
As you can see based on the previous output of the RStudio console, our example file has three worksheets named Sheet1, Sheet2, and Sheet3.
In the next step, we can use the lapply, as.data.frame, and read_excel functions to import our data to R:
list_all <- lapply(sheet_names, function(x) { # Read all sheets to list as.data.frame(read_excel("data_all.xlsx", sheet = x)) } ) |
list_all <- lapply(sheet_names, function(x) { # Read all sheets to list as.data.frame(read_excel("data_all.xlsx", sheet = x)) } )
The previous R code has created a new list object called list_all, which contains all worksheets of our excel file converted as data frame objects.
It makes sense to rename the list elements of our list by the sheet names of our Excel file:
names(list_all) <- sheet_names # Rename list elements |
names(list_all) <- sheet_names # Rename list elements
That’s it! Now, we can have a look at each of the elements of our list.
The first six rows of our Sheet1 data look as follows…
head(list_all$Sheet1) # Print head of first sheet |
head(list_all$Sheet1) # Print head of first sheet
…the head of the second sheet looks as follows…
head(list_all$Sheet2) # Print head of second sheet |
head(list_all$Sheet2) # Print head of second sheet
…and the third sheet has the following data structure:
head(list_all$Sheet3) # Print head of third sheet |
head(list_all$Sheet3) # Print head of third sheet
Looks good!
Video, Further Resources & Summary
Some time ago I have published a video on my YouTube channel, which explains the R code of this tutorial. You can find the video below:
The YouTube video will be added soon.
In addition, you may want to read the other tutorials of this website. Some tutorials are listed below.
- Write xlsx & xls in R
- Read Fixed Width Text File in R
- scan Function (5 Example Codes)
- Read xlsx & xls Excel File in R
- R Programming Examples
In this article you have learned how to create a list of data frames based on an Excel file in R programming. Please tell me about it in the comments section, if you have further questions.