CODEWITHSUNDEEP
r
J0HN_TIT0R
J0HN_TIT0R

Reputation: 323

How to preserve empty rows when importing xlsx files into R with read.xlsx?

I'd like to be able to import xlsx files of varying lengths into R. I'm currently using the function read.xlsx from R's xlsx package to import the xlsx files into R, and unfortunately it drops empty rows. Is there a way that I can import every row of an xlsx file up until the last row with content without dropping empty rows?

Upvotes: 0

Views: 2413

Answers (3)

r2evans
r2evans

Reputation: 160952

That package has not been updated since 2014 (CRAN, though it looks like there has been some work in 2017 at https://github.com/dragua/xlsx), I suggest either readxl or openxlsx:

readxl::read_excel("file_with_blank_row.xlsx")
openxlsx::read.xlsx("file_with_blank_row.xlsx", skipEmptyRows=FALSE)

Minor update (Mar 2024): the xlsx package was updated on CRAN on 2020-11-10, and while there was some activity in 2022, it does not seem to have come to fruition. I don't know if the OP's issues remain, but I still recommend one of readxl, openxlsx, or a newcomer to the fray, openxlsx2, a modernization effort for the openxlsx package.

Upvotes: 6

Fons MA
Fons MA

Reputation: 1282

As noted by r2evans, both readxl and openxlsx have options to turn off skipping of empty rows. However, regardless of those switches, they will silently drop leading empty rows.

openxlsx doesn't seem to offer the possibility of altering that behaviour.

readxl has a range parameter that will indeed keep all empty rows. This is necessary if you're hoping to edit the same Excel file in very specific locations.

You need to have something like readxl::read_excel("path_to_your.xlsx", range = cell_limits(c(1, NA), c(NA,NA)). Using NA for all values apparently causes the function to revert to default and drop leading empty rows.

Upvotes: 1

Gracie
Gracie

Reputation: 1

Try this:

library("readxl")
my_data <- read_xlsx("file_with_blank_row.xlsx")

Upvotes: -1

Related Questions