There are plenty of ways to import an Excel file into R. Most of them exist in the form of R packages that you must install on your system. Scroll down to learn more about them.
Import An Excel File Into R
This package is probably the most popular method of reading data from Excel into R. Unlike options with the same purposes we are going to introduce below, it requires no external dependencies.
The readxl package is compatible with both the legacy (.xls) and modern (.xlsx) formats of Excel. It makes use of the libxls C library under the hood to deal with .xsl files – the binary format that features many complexities. Meanwhile, the RapidXML C++ library is used to parse the XML-based .xlsx files.
To use the readxl package, you can install it directly or install the tidyverse package from CRAN:
It can also be installed from the GitHub repository of tidyverse, which provides the development version of readxl:
Note: even when the tidyverse package has been loaded, you still need to import readxl separately.
In the R console or your script, include the package:
In these examples, we are going to use the homes.xlsx file that contains information on some home sales, such as their listing and selling prices.
The function read_excel() can detect .xls and .xlsx files from the path you provide.
The function excel_sheets() will show you list sheets in the Excel file:
When an Excel file has multiple worksheets, you can import data from a specific sheet by using its name:
read_excel("homes.xlsx", sheet = "homes")
You can even control which subset of the Excel file you want to import by providing a range of cells:
read_excel("homes.xlsx", range = "C1:G6")
You can limit the range of rows or columns that need to be imported into R in the same manner:
read_excel("homes.xlsx", range = cell_rows(1:5))
read_excel("homes.xlsx", range = cell_cols("A:D"))
This package comes with R functions that can read and write Excel files. It needs a Java Development Kit installation to run, so you should have it ready on your system.
Install xlsx from CRAN (stable version):
If you want to try the development version, install xlsx from GitHub:
You can read the first sheet of the homes.xlsx file above:
Another package that can read, write, and style Excel files is openxlsx.
You can install its stable version from CRAN:
install.packages("openxlsx", dependencies = TRUE)
Or choose the development version:
install.packages(c("Rcpp", "remotes"), dependencies = TRUE) remotes::install_github("ycphs/openxlsx")
The openxlsx package also provides a function called
read.xlsx() like xlsx. They have some differences in syntax. If you load the xlsx package first in the same season, the function
read.xlsx() will be masked by it. This package should be unloaded from R first, and then you can load the openxlsx package in a normal way:
detach("package:xlsx", unload=TRUE) library(openxlsx)
Load the homes.csv file:
You can also specify which sheet, rows, and columns to load:
read.xlsx("homes.xlsx", sheet = 1, cols = c(1:4), rows = c(1:5))
There are many packages that can help you import an Excel file into R. You can install them from CRAN and load only a subset or the entire data from the source file.