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.
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",
path_col = NULL,
workers = 1L,
verbose = FALSE,
on_error = "stop",
...
)Non-empty character vector of paths to existing
.xlsx / .xls files.
logical(1). TRUE (default) binds all sheets
into a single data.table. FALSE returns a
flat named list keyed as "<filename>_<sheetname>".
Positive integer vector, character vector of
sheet names, or NULL (default, every sheet).
Indices / names must exist in all supplied files.
Non-negative integer(1). Number of rows to skip
before the header (e.g. a title row). Default 0L.
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.
character(1). Separator used to join the levels of
a multi-row header. Default "_".
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.
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).
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.
NULL (default) or character(1). Name of an
extra column holding the full path of the source file
(absolute, "/" separators) when
combine = TRUE. Useful when folder names carry
information (farm, year, batch) that can then be
extracted with get_path_info(). Remove it before
export_xlsx() if it should not be written out.
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.
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.
"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. Unless guess_max is given,
column types are guessed from all rows (readxl's
default looks at the first 1000 only, so a column that
is empty there would be read as logical and its later
values lost). This costs a few percent of read time and
no extra memory.
combine = TRUEA data.table. Tracking columns
excel_name and/or sheet_name are prepended when their
respective file_col / sheet_col are not NULL.
combine = FALSEA named list of data.tables,
each element named "<filename>_<sheetname>". The list carries a
"source_files" attribute with the original file paths.
Combining sheets with different column types. When
combine = TRUE, columns are aligned before binding:
A column that is completely empty in one sheet (read as logical
NA) takes the type it has in the other sheets, e.g. stays a
date column.
A column whose type genuinely differs between sheets (e.g. a date
in one workbook, text such as "2024.3.1" in another; or numbers
vs. text) is converted to text in all sheets, with one warning naming
the column and where each type occurs. Dates are written as
"YYYY-MM-DD" (with the time when it is not midnight). Integer /
double / logical columns are combined as numbers, without a warning.
Without this step data.table::rbindlist() stops for any date column
that is empty or text in another sheet.
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.
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.
# 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
)
#> excel_name sheet_name col1 col2 col3
#> <char> <char> <num> <char> <lgcl>
#> 1: xlsx_test1 Sheet1 4 d FALSE
#> 2: xlsx_test1 Sheet1 5 f TRUE
#> 3: xlsx_test1 Sheet1 6 e TRUE
#> 4: xlsx_test1 Sheet2 1 a TRUE
#> 5: xlsx_test1 Sheet2 2 b FALSE
#> 6: xlsx_test1 Sheet2 3 c TRUE
#> 7: xlsx_test2 Sheet1 15 o FALSE
#> 8: xlsx_test2 Sheet1 16 p TRUE
#> 9: xlsx_test2 Sheet1 17 q FALSE
#> 10: xlsx_test2 a 7 g FALSE
#> 11: xlsx_test2 a 9 h TRUE
#> 12: xlsx_test2 a 8 i FALSE
#> 13: xlsx_test2 b 10 J FALSE
#> 14: xlsx_test2 b 11 K TRUE
#> 15: xlsx_test2 b 12 L FALSE
# 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
)
#> $xlsx_test1_Sheet2
#> col1 col2 col3
#> <num> <char> <lgcl>
#> 1: 1 a TRUE
#> 2: 2 b FALSE
#> 3: 3 c TRUE
#>
#> $xlsx_test2_a
#> col1 col2 col3
#> <num> <char> <lgcl>
#> 1: 7 g FALSE
#> 2: 9 h TRUE
#> 3: 8 i FALSE
#>
#> attr(,"source_files")
#> [1] "/home/runner/work/_temp/Library/mintyr/extdata/xlsx_test1.xlsx"
#> [2] "/home/runner/work/_temp/Library/mintyr/extdata/xlsx_test2.xlsx"
# 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
)
#> excel_name sheet_name ID Breed Birth date Weight (kg)_Start
#> <char> <char> <char> <char> <POSc> <num>
#> 1: multiheader_test farm_A A001 Duroc 2024-01-05 28.5
#> 2: multiheader_test farm_A A002 Duroc 2024-01-07 30.1
#> 3: multiheader_test farm_A A003 Landrace 2024-01-10 27.9
#> 4: multiheader_test farm_A A004 Landrace 2024-01-12 29.4
#> 5: multiheader_test farm_B B001 Yorkshire 2024-02-01 29.0
#> 6: multiheader_test farm_B B002 Yorkshire 2024-02-03 27.6
#> 7: multiheader_test farm_B B003 Duroc 2024-02-04 31.2
#> Weight (kg)_End Backfat (mm)_P2 Backfat (mm)_Loin Note
#> <num> <num> <num> <char>
#> 1: 102.3 11.2 56.1 <NA>
#> 2: 108.7 12.5 58.3 lame
#> 3: 99.5 10.8 54.7 <NA>
#> 4: 104.2 11.9 57.0 <NA>
#> 5: 101.8 12.1 55.4 <NA>
#> 6: 98.4 10.9 53.9 <NA>
#> 7: 110.5 13.0 59.2 culled
# 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"))
#> [1] "excel_name" "sheet_name" "ID"
#> [4] "Breed" "Birth date" "Weight (kg)_Start"
#> [7] "Weight (kg)_End" "Backfat (mm)_P2" "Backfat (mm)_Loin"
#> [10] "Backfat (mm)_Note"
# 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)
#> [1] "multiheader_test"
unlink(bad_file)
# Example 5: Keep the full path and extract information from folder names
# (e.g. <root>/<farm>/<year>/file.xlsx)
root <- file.path(tempdir(), "farms")
for (p in c("A/2024", "B/2025")) dir.create(file.path(root, p), recursive = TRUE)
file.copy(mintyr_example("xlsx_test1.xlsx"), file.path(root, "A/2024/growth_a.xlsx"))
#> [1] TRUE
file.copy(mintyr_example("xlsx_test2.xlsx"), file.path(root, "B/2025/growth_b.xlsx"))
#> [1] TRUE
growth <- import_xlsx(
list.files(root, pattern = "\\.xlsx$", recursive = TRUE, full.names = TRUE),
path_col = "file_path" # Full path of every row's source file
)
growth[, farm := get_path_info(file_path, n = -3)] # third-last part: farm
#> excel_name sheet_name file_path col1
#> <char> <char> <char> <num>
#> 1: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 4
#> 2: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 5
#> 3: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 6
#> 4: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 1
#> 5: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 2
#> 6: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 3
#> 7: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 15
#> 8: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 16
#> 9: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 17
#> 10: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 7
#> 11: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 9
#> 12: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 8
#> 13: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 10
#> 14: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 11
#> 15: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 12
#> col2 col3 farm
#> <char> <lgcl> <char>
#> 1: d FALSE A
#> 2: f TRUE A
#> 3: e TRUE A
#> 4: a TRUE A
#> 5: b FALSE A
#> 6: c TRUE A
#> 7: o FALSE B
#> 8: p TRUE B
#> 9: q FALSE B
#> 10: g FALSE B
#> 11: h TRUE B
#> 12: i FALSE B
#> 13: J FALSE B
#> 14: K TRUE B
#> 15: L FALSE B
growth[, year := get_path_info(file_path, n = -2)] # second-last part: year
#> excel_name sheet_name file_path col1
#> <char> <char> <char> <num>
#> 1: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 4
#> 2: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 5
#> 3: growth_a Sheet1 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 6
#> 4: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 1
#> 5: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 2
#> 6: growth_a Sheet2 /tmp/RtmpBFC2vg/farms/A/2024/growth_a.xlsx 3
#> 7: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 15
#> 8: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 16
#> 9: growth_b Sheet1 /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 17
#> 10: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 7
#> 11: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 9
#> 12: growth_b a /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 8
#> 13: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 10
#> 14: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 11
#> 15: growth_b b /tmp/RtmpBFC2vg/farms/B/2025/growth_b.xlsx 12
#> col2 col3 farm year
#> <char> <lgcl> <char> <char>
#> 1: d FALSE A 2024
#> 2: f TRUE A 2024
#> 3: e TRUE A 2024
#> 4: a TRUE A 2024
#> 5: b FALSE A 2024
#> 6: c TRUE A 2024
#> 7: o FALSE B 2025
#> 8: p TRUE B 2025
#> 9: q FALSE B 2025
#> 10: g FALSE B 2025
#> 11: h TRUE B 2025
#> 12: i FALSE B 2025
#> 13: J FALSE B 2025
#> 14: K TRUE B 2025
#> 15: L FALSE B 2025
growth[, .(excel_name, farm, year)]
#> excel_name farm year
#> <char> <char> <char>
#> 1: growth_a A 2024
#> 2: growth_a A 2024
#> 3: growth_a A 2024
#> 4: growth_a A 2024
#> 5: growth_a A 2024
#> 6: growth_a A 2024
#> 7: growth_b B 2025
#> 8: growth_b B 2025
#> 9: growth_b B 2025
#> 10: growth_b B 2025
#> 11: growth_b B 2025
#> 12: growth_b B 2025
#> 13: growth_b B 2025
#> 14: growth_b B 2025
#> 15: growth_b B 2025
unlink(root, recursive = TRUE)