remove empty columns ?

[stla]

Hello,

I have an Excel file containing an empty column but the cells in this column have a format.
This column is imported by read_excel:

> dd <- read_excel("Book1.xlsx", sheet=1)
> names(dd)
[1] "Batch"             "Glycoprotein Bulk" "Residual Moisture"
[4] "Est Potency"       ""
> summary(dd[,5])
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
     NA      NA      NA      NA      NA      NA    "83" 
> str(dd[,5])
 POSIXct[1:83], format: NA NA NA NA NA NA NA NA NA NA NA NA NA ...

Of course I can get rid of this column, but maybe it would be nice if read_excel automatically got rid of it ?

I can share the XLSX file if needed.

[jennybc]

An example xlsx is always helpful. Boilerplate reprex advice follows, in case any Qs.

How to provide a readxl reprex

We're in a much better position to address your issue if you can provide a reprex (reproducible example). Provide as much of this as you can:

• An actual xls or xlsx file. Pick one:
  • Your personal xls or xlsx: try to strip it down to the minimal size and complexity to demonstrate your point. And, obviously, remove any sensitive data.
  • A publicly available xls or xlsx: provide URL and the code you used to download.
• A small bit of R code that uses readxl on the provided xls or xlsx file and demonstrates your point.
  • Consider using the reprex package to prepare this. In addition to nice formatting, this ensures your reprex is self-contained.
• Any details about your environment that seem clearly relevant, such as operating system.
  reprex(..., si = TRUE)
  will append a standard summary, folded neatly away, at the bottom of your reprex.

How to provide your own xls/xlsx file? In order of preference:

1. Attach the file directly to your issue. Instructions are always at the bottom of the issue or comment box.
2. Share via DropBox or Google Drive and provide the link in your issue.
3. Explain you absolutely cannot provide a relevant file via github.com and offer to provide privately.
4. Don't share a file and realize you're hoping for, e.g., a bug fix with no concrete example to go on.

[stla]

Hello @jennybc. I always prefer to wait before sharing a file, in case one says me that my report is a duplicate.

Thank you for the procedure, I never saw it before.

But I've sized the opportunity to give my first try to the openxlsx package (a package I discovered thanks to you). Thus I don't need to share a file. Below is my reproducible example:

library(openxlsx)
dd <- data.frame(A=1:2, B=3:4)
wb <- createWorkbook()
addWorksheet(wb=wb, sheetName = "Sheet1")
# add a numFmt in the empty column 3
sty <- createStyle(numFmt = "yyyy/mm/dd")
addStyle(wb, 1, style = sty, rows = 2:3, cols = 3)
writeData(wb, "Sheet1", dd)
saveWorkbook(wb, "test00.xlsx", overwrite = TRUE) 

Now:

> readxl::read_excel("test00.xlsx", sheet=1)
# A tibble: 2 × 3
      A     B     ``
  <dbl> <dbl> <dttm>
1     1     3   <NA>
2     2     4   <NA>

[jennybc]

Duplicate of #162