Summarise date-formatted cells by column — excel_date

A column-level view of excel_date_cells(), for when a workbook has already been imported with another reader and only the metadata is needed. The name and field_order columns say which imported columns to repair and in which order to read the fields, so restore_day_month() can be applied to exactly those columns with nothing left to infer.

Usage

excel_date_columns(path, sheet = 1, col_names = TRUE)

Arguments

path: Path to an .xlsx file.
sheet: Sheet name, or its index in the workbook. Defaults to the first sheet.
col_names: If TRUE (default), the first row of the sheet holds column names, as it would for utils::read.csv() or readxl::read_excel().

Value

A data frame with one row per column containing at least one date-formatted cell: col (index), name, n_date (date-formatted cells), n_values (populated cells below the header), prop_date, field_order, and format_code.

Examples

path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")
cols <- excel_date_columns(path)
cols
#>   col  name n_date n_values prop_date field_order format_code
#> 1   2  dose      5        5         1          dm    d/m/yyyy
#> 2   5 visit      5        5         1          md    mm-dd-yy

# Repair a frame imported by any other reader, with no guessing left:
df <- unexcel_xlsx(path, restore = FALSE)
for (i in seq_len(nrow(cols))) {
  df[[cols$name[i]]] <- restore_day_month(
    df[[cols$name[i]]],
    date_system = excel_date_system(path),
    order = cols$field_order[i]
  )
}