Unit 03.03: A spreadsheet is a document, not a data file

A spreadsheet is not a data file. It is a document that happens to contain some data, usually not on the first sheet.

Name the sheet, then check the types

Real workbooks carry title rows, notes, merged headers, several sheets and colour-coded meaning that no import can see. read_excel() reads the first sheet by default, and the first sheet is very often a cover note.

The deeper difference is that a spreadsheet's types are per cell, not per column. A column can hold numbers with three text cells in the middle, and the file is perfectly valid. Import then has to decide, and it decides based on what it sees.

Where you have a choice, ask for a CSV. Where you do not, name the sheet explicitly, name the range if the data does not start at the top left, and check the resulting types before trusting anything.

This block writes a two-sheet workbook whose first sheet is prose, then reads it both ways.

suppressPackageStartupMessages({library(writexl); library(readxl)})

path <- file.path(tempdir(), "ward-return.xlsx")
write_xlsx(list(
  notes  = data.frame(text = "Return for January. Do not edit."),
  visits = data.frame(ward = c("North", "South", "East"),
                      visits = c(412, 388, 502))
), path)

cat("Sheets in the file:", paste(excel_sheets(path), collapse = ", "), "\n\n")

# Reading the first sheet is the default, and here it is the wrong one.
cat("Default read gives", ncol(read_excel(path)), "column of prose.\n\n")

visits <- read_excel(path, sheet = "visits")
print(visits)

# The real hazard is that a spreadsheet's types are per-cell, not per-column.
cat("\nName the sheet, name the range if you must, and check the types:\n")
cat(paste(names(visits), sapply(visits, function(x) class(x)[1]),
          sep = " = ", collapse = ", "), "\n")

The sheets are notes and visits. The default read returns 1 column of prose — no error, just the wrong sheet, which is exactly how this fails in practice. Naming the sheet gives the three-row table, and the type check confirms ward = character, visits = numeric. Skipping that check is how a numeric column with one stray note in it becomes text without anyone noticing.

The mistake this prevents

The mistake is reading a workbook without naming the sheet and assuming the result is the data. It runs, it returns a table, and the table is the cover note.

Takeaway

Ask for CSV when you can. Otherwise name the sheet, verify the column types after reading, and treat anything that arrived as text but should be numeric as a question about the source file.

Mark complete