import_xlsx: Import Data from XLSX Files

In mintyr: Grouped and Nested Data Pipelines Built on 'data.table'

Import Data from XLSX Files

Description

A high-performance function for importing data from one or multiple Excel files into data.table format, with fine-grained control over source tracking columns, sheet selection, row skipping, multi-row headers, and optional parallel reading across (file, sheet) pairs.

Performance characteristics:

• excel_sheets() called exactly once per file (cached).

• The real cost of an Excel import is read_excel() parsing, which is single-threaded C++ and not affected by the data.table thread pool. The flat (file x sheet) task list is therefore read in parallel across processes when workers > 1: a fork pool on Unix/macOS, a PSOCK cluster on Windows. The cluster / fork pool is always torn down on exit.

• setDT() converts tibbles in-place — zero vector copies.

• Tracking columns injected via := on small per-sheet tables before the single final rbindlist.

• The final rbindlist uses the ambient data.table thread pool; tune it globally with setDTthreads if needed. Workers are pinned to a single thread during the read phase so parallel processes do not oversubscribe cores.

Usage

import_xlsx(
  file,
  combine = TRUE,
  sheet = NULL,
  skip = 0L,
  header_rows = 1L,
  header_sep = "_",
  header_fill = "auto",
  file_col = "excel_name",
  sheet_col = "sheet_name",
  workers = 1L,
  verbose = FALSE,
  on_error = "stop",
  ...
)

Arguments

file	Non-empty character vector of paths to existing .xlsx / .xls files.
combine	logical(1). TRUE (default) binds all sheets into a single data.table. FALSE returns a flat named list keyed as "<filename>_<sheetname>".
sheet	Positive integer vector, character vector of sheet names, or NULL (default, every sheet). Indices / names must exist in all supplied files.
skip	Non-negative integer(1). Number of rows to skip before the header (e.g. a title row). Default 0L.
header_rows	Positive integer(1). Number of header rows below the skipped rows. Default 1L (ordinary sheets). Values > 1 combine the rows into one name per column (see Details); data start on the next row.
header_sep	character(1). Separator used to join the levels of a multi-row header. Default "_".
header_fill	How merged header cells are resolved when header_rows > 1: "auto" (default) reads the merged ranges stored in .xlsx/.xlsm files and falls back to "right" for .xls; "right" fills upper levels to the right (heuristic, no merge information needed); "none" uses the cells as they are.
file_col	character(1) or NULL. Name of the column recording the source file (extension stripped) when combine = TRUE. Default "excel_name"; NULL omits the column. Ignored when combine = FALSE (provenance is in the list names).
sheet_col	character(1) or NULL. Name of the column recording the source sheet when combine = TRUE. Default "sheet_name"; NULL omits it. The defaults match export_xlsx(), so the result can be written back to the original layout.
workers	integer(1). Number of parallel processes used to read the (file x sheet) tasks. Default 1L (serial). Values > 1 open a fork pool on Unix/macOS or a PSOCK cluster on Windows; the pool is capped at the number of tasks and always shut down on exit. Parallel reading pays off when files are large or numerous; for many tiny sheets the process / serialization overhead can dominate, so leave this at 1L unless the import is heavy.
verbose	logical(1). When TRUE, print one message() per sheet (source file, sheet name, and row x col dimensions, with empty sheets flagged) followed by a summary line. Default FALSE. Output is emitted from the master process, so it surfaces identically in serial and parallel modes.
on_error	"stop" (default) or "warn". With "stop", any file or sheet that cannot be read aborts the import with one error listing all failures. With "warn", failed files/sheets are skipped and listed in a single warning; the import only fails when nothing could be read.
...	Additional arguments forwarded to read_excel (e.g. col_types, na, trim_ws). Do not pass path, sheet, or skip here; use the dedicated parameters above. With header_rows > 1, col_names and range are set internally and must not be passed either; col_types then refers to the data columns.

Details

Multi-row headers. Many hand-made workbooks have a title row and a header spread over several rows with merged cells, e.g.

row 1  Growth test records
row 2  ID | Breed | Weight (kg)   | Backfat (mm)
row 3     |       | Start | End   | P2 | Loin
row 4  A001 | Duroc | 28.5 | 102.3 | 11.2 | 56.1

import_xlsx(f, skip = 1, header_rows = 2) returns the columns ID, Breed, Weight (kg)_Start, Weight (kg)_End, Backfat (mm)_P2, Backfat (mm)_Loin. The rules are:

• Merged cells only store their value in their top-left cell. With header_fill = "auto" the merged ranges are read from the workbook itself (<mergeCell> entries of the sheet XML), and each range is filled with its value, horizontally and vertically. Header cells that are genuinely empty stay empty, so a column whose upper cell is blank gets only its lower label.

• Without merge information (.xls files, or header_fill = "right"), every level except the last is filled to the right, restarting wherever a higher level starts a new label.

• Per column, empty parts and repeated parts (vertically merged cells) are dropped and the remaining parts joined with header_sep; line breaks inside a cell become spaces.

• Columns without any header become col_<j>; duplicates are made unique.

• The header block is read as text, but the data block is read separately, so numbers and dates keep their types (reading the whole sheet as text would turn dates into serial numbers such as "45296").

• Completely empty data rows (spacer or trailing rows) are dropped.

Labels that are only visually spread over several columns with "Center Across Selection" (instead of real merging) are not merges; use header_fill = "right" for such sheets. Reading the merge information re-scans the sheet XML once, which costs a fraction of the import time and only happens when header_rows > 1.

Value

combine = TRUE

A data.table. Tracking columns excel_name and/or sheet_name are prepended when their respective file_col / sheet_col are not NULL.

combine = FALSE

A named list of data.tables, each element named "<filename>_<sheetname>". The list carries a "source_files" attribute with the original file paths.

Note

Files that share the same base name in different folders cannot be told apart by file_col; a warning is issued and the labels are made unique, so a later export_xlsx() round trip does not merge them.

Examples

# Example: Excel file import demonstrations

# Setup test files
xlsx_files <- mintyr_example(
  mintyr_examples("xlsx_test")    # Get example Excel files
)

# Example 1: Import and combine all sheets from all files
import_xlsx(
  xlsx_files,                     # Input Excel file paths
  combine = TRUE                  # Combine all sheets into one data.table
)

# Example 2: Import specific sheets separately
import_xlsx(
  xlsx_files,                     # Input Excel file paths
  combine = FALSE,                 # Keep sheets as separate data.tables
  sheet = 2                       # Only import the second sheet
)

# Example 3: Multi-row header with merged cells and a title row
# (row 1 = title, rows 2-3 = header, data from row 4)
mh_file <- mintyr_example("multiheader_test.xlsx")
import_xlsx(
  mh_file,
  skip = 1,                       # Skip the title row
  header_rows = 2                 # Combine two header rows into one name
)
# The last column has an empty, unmerged upper cell: it is named "Note".
# The heuristic fill would wrongly call it "Backfat (mm)_Note":
names(import_xlsx(mh_file, sheet = 1, skip = 1, header_rows = 2,
                  header_fill = "right"))

# Example 4: Batch import that skips unreadable files instead of stopping
bad_file <- tempfile(fileext = ".xlsx")
writeLines("not a workbook", bad_file)
res <- suppressWarnings(
  import_xlsx(c(mh_file, bad_file), skip = 1, header_rows = 2, on_error = "warn")
)
unique(res$excel_name)
unlink(bad_file)

mintyr documentation built on Oct. 5, 2026, 5:08 p.m.