How to Use read.xlsx() Function in R

by

The read.xlsx() function in R is “used to read data from an Excel file into a data frame”. It is part of the xlsx package, which can be installed from CRAN.

Syntax

read.xlsx(
   xlsxFile,
   sheet = 1,
   startRow = 1,
   colNames = TRUE,
   rowNames = FALSE,
   detectDates = FALSE,
   skipEmptyRows = TRUE,
   skipEmptyCols = TRUE,
   rows = NULL,
   cols = NULL,
   check.names = FALSE,
   sep.names = ".",
   namedRegion = NULL,
   na.strings = "NA",
   fillMergedCells = FALSE
)

Parameters

xlsxFile: It is an xlsx file or URL to xlsx file.

sheet: It is the name or index of the sheet to read the data.

startRow: It is the first row to begin looking for data. If it starts with empty rows, the rows at the top of a file are always skipped, despite the value of the startRow.

colNames: If TRUE, the first row of data will be used as column names.

rowNames: If  TRUE, the first column of data will be used as row names.

detectDates: If TRUE, attempt to understand the dates and perform the conversion.

skipEmptyRows: If it is TRUE, then empty rows are skipped; otherwise, empty rows after the first row containing data will return a row of NAs(Not Available).

skipEmptyCols: If it is TRUE, empty columns are skipped.

rows: A numeric vector defines which rows in the Excel file to read. If NULL, all rows are read.

cols: It is a numeric vector defining which columns in the Excel file to read. If NULL, all columns are read.

check.names: It is a logical argument. If TRUE, then the names of the variables in the data frame are checked to ensure that they are syntactically valid variable names.

sep.names: A character that substitutes blanks in column names. By default, “.”

namedRegion: It is a named region in the Workbook. If not NULL startRow, rows, and cols parameters are ignored.

na.strings: It is a character vector of strings to be interpreted as NA. Blank cells will be returned as NA.

fillMergedCells: If it is TRUE, the value in a merged cell is given to all cells within it.

Example

To work with Excel files in R, you need to have a demo Excel file that contains some sample data.

For this example, I am using the demo_data.xlsx file.

To import the openxlsx package in R, use the following code.

library("openxlsx")

Now, you can use the read.xlsx() function and pass the external excel file path to read the file in xlsx format.

So your code looks like the following.

library("openxlsx")

df1 <- read.xlsx(xlsxFile = "demo_data.xlsx", sheet = 1, skipEmptyRows = FALSE)
df1

After running the above code, if you face this error, you cannot find function read.xlsx in r, which means the package is not installed on your machine. To solve this error, you need to install the openxlsx package.

You can also face the following error.

Error in loadWorkbook(file, password = password): argument “file” is missing, with no default Calls: read.xlsx -> loadWorkbook -> file.exists -> path.expand Execution halted

If you face this error, it may be a problem with your installation of the openxlsx package. To solve this error, you need to reinstall the openxlsx package with a new R session.

   0 First.Name Last.Name  Gender  Country       Age  Date      Id
1  1    Dulce      Abril    Female United States 32 15/10/2017 1562
2  2    Mara      Hashimoto Female Great Britain 25 16/08/2016 1582
3  3    Philip    Gent      Male   France        36 21/05/2015 2587
4  4    Kathleen  Hanner    Female United States 25 15/10/2017 3549
5  5    Nereida   Magwood   Female United States 58 16/08/2016 2468
6  6    Gaston    Brumm     Male   United States 24 21/05/2015 2554
7  7    Etta      Hurn      Female Great Britain 56 15/10/2017 3598
8  8    Earlean   Melgar    Female United States 27 16/08/2016 2456
9  9    Vincenza  Weiland   Female United States 40 21/05/2015 6548

could not find function “read.xlsx”

The error could not find function “read.xlsx” occurs when R cannot find the read.xlsx() function in the current environment or package.

To fix the could not find function “read.xlsx” error, you need to install the “openxlsx” library and load the package at the start of the R file like this: library(openxlsx). This will help R find the read.xlsx() function from the “openxlsx” package.

That is it.

See also

read.csv() function in R

write.csv() function in R

Leave a Comment