mintyr reads many files into one data.table and writes
the pieces back out. import_xlsx() /
import_csv() add source columns (excel_name,
sheet_name, …) so that export_xlsx() can
rebuild the original file / sheet layout; export_nest() and
export_list() write one text file per group, e.g. as input
for command-line breeding software.
import_xlsx()# 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] "C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/xlsx_test1.xlsx"
#> [2] "C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/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)import_csv()# Example: CSV file import demonstrations
# Setup test files
csv_files <- mintyr_example(
mintyr_examples("csv_test") # Get example CSV files
)
# Example 1: Import and combine CSV files using data.table
import_csv(
csv_files, # Input CSV file paths
combine = TRUE, # Combine all files into one data.table
file_col = "_file", # Column name for file source
keep_ext = TRUE, # Include .csv extension in _file column
full_path = TRUE # Show complete file paths in _file column
)
#> _file
#> <char>
#> 1: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test1.csv
#> 2: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test1.csv
#> 3: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test1.csv
#> 4: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test2.csv
#> 5: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test2.csv
#> 6: C:/Users/tony2/AppData/Local/Temp/Rtmp6psovW/Rinst8d44679c53fa/mintyr/extdata/csv_test2.csv
#> col1 col2 col3
#> <int> <char> <lgcl>
#> 1: 4 d FALSE
#> 2: 5 f TRUE
#> 3: 6 e TRUE
#> 4: 15 o FALSE
#> 5: 16 p TRUE
#> 6: 17 q FALSEexport_xlsx()# Example 1: A plain data.frame -> one workbook, one sheet
out_file <- file.path(tempdir(), "mtcars.xlsx")
export_xlsx(mtcars, path = out_file, sheet_name = "mtcars")
invisible(file.remove(out_file))
# Example 2: data.table input works exactly the same way
out_file <- file.path(tempdir(), "mtcars_dt.xlsx")
export_xlsx(data.table::as.data.table(mtcars),
path = out_file, sheet_name = "mtcars")
invisible(file.remove(out_file))
# Example 3: One sheet per group in a single workbook
# Each Species value becomes a sheet; keep the Species column
out_file <- file.path(tempdir(), "iris_by_species.xlsx")
export_xlsx(iris, path = out_file, file_col = "Species", drop_cols = FALSE)
invisible(file.remove(out_file))
# Example 4: One file per group (directory mode: no .xlsx extension)
out_dir <- file.path(tempdir(), "iris_by_species")
out_files <- export_xlsx(iris, path = out_dir, file_col = "Species")
basename(out_files)
#> [1] "setosa.xlsx" "versicolor.xlsx" "virginica.xlsx"
unlink(out_dir, recursive = TRUE)
# Example 5: Round-trip the layout produced by import_xlsx(combine = TRUE)
# Rows are routed back to their original file and sheet
combined <- data.frame(
excel_name = c("sales", "sales", "costs"),
sheet_name = c("2024", "2025", "2024"),
amount = c(100, 120, 80)
)
out_dir <- file.path(tempdir(), "roundtrip")
out_files <- export_xlsx(combined, path = out_dir) # sales.xlsx, costs.xlsx
basename(out_files)
#> [1] "sales.xlsx" "costs.xlsx"
unlink(out_dir, recursive = TRUE)
# Example 6: Named list -> single workbook, one sheet per element
out_file <- file.path(tempdir(), "combined.xlsx")
res <- list(res1 = iris, res2 = mtcars)
export_xlsx(res, path = out_file)
invisible(file.remove(out_file))
# Example 7: Named list -> directory, one file per element
out_dir <- file.path(tempdir(), "combined")
out_files <- export_xlsx(res, path = out_dir) # res1.xlsx, res2.xlsx
basename(out_files)
#> [1] "res1.xlsx" "res2.xlsx"
unlink(out_dir, recursive = TRUE)export_nest()# Example: Basic nested data export workflow
# A dedicated sub-folder of tempdir() keeps the clean-up safe
out_dir <- file.path(tempdir(), "mintyr_export_nest")
# Step 1: Create nested data structure
dt_nest <- w2l_nest(
data = iris, # Input iris dataset
cols = 1:2, # Columns to be nested
by = "Species" # Grouping variable
)
# Step 2: Export nested data to files
files <- export_nest(
data = dt_nest, # Input nested data.table
cols = "data", # Column containing nested data
by = c("name", "Species"), # Columns to create directory structure
path = out_dir
)
#> [ export_nest ] Export complete. 6 file(s) written to: C:\Users\tony2\AppData\Local\Temp\Rtmpsxilk2/mintyr_export_nest
# Returns (invisibly) the paths of the written files
# Directory structure: out_dir/<name>/<Species>/data.txt
files
#> [1] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Length/setosa/data.txt"
#> [2] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Length/versicolor/data.txt"
#> [3] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Length/virginica/data.txt"
#> [4] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Width/setosa/data.txt"
#> [5] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Width/versicolor/data.txt"
#> [6] "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_nest/Sepal.Width/virginica/data.txt"
# Clean up
unlink(out_dir, recursive = TRUE)export_list()# Example: Export split data to files
out_dir <- file.path(tempdir(), "mintyr_export_list")
# Step 1: Create split data structure
dt_split <- w2l_split(
data = iris, # Input iris dataset
cols = 1:2, # Columns to be split
by = "Species" # Grouping variable
)
# Step 2: Export split data to files
files <- export_list(
data = dt_split, # Input list of data.tables
path = out_dir
)
#> [ export_list ] Export complete. 6 / 6 file(s) written to: C:\Users\tony2\AppData\Local\Temp\Rtmpsxilk2/mintyr_export_list
# Returns (invisibly) a named vector of the written file paths
files
#> Sepal.Length_setosa
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Length_setosa.txt"
#> Sepal.Length_versicolor
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Length_versicolor.txt"
#> Sepal.Length_virginica
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Length_virginica.txt"
#> Sepal.Width_setosa
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Width_setosa.txt"
#> Sepal.Width_versicolor
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Width_versicolor.txt"
#> Sepal.Width_virginica
#> "C:\\Users\\tony2\\AppData\\Local\\Temp\\Rtmpsxilk2/mintyr_export_list/Sepal.Width_virginica.txt"
# Clean up
unlink(out_dir, recursive = TRUE)