--- title: "Import and export" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Import and export} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r, include = FALSE} knitr::opts_chunk$set( collapse = TRUE, comment = "#>" ) ``` ```{r} library(mintyr) ``` 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. # Read many Excel workbooks: `import_xlsx()` ```{r example-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 ) # 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) ``` # Read many CSV files: `import_csv()` ```{r examples-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 ) ``` # Write tables back to Excel: `export_xlsx()` ```{r example-export_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) 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) 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) unlink(out_dir, recursive = TRUE) ``` # Write a nested table to folders: `export_nest()` ```{r example-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 ) # Returns (invisibly) the paths of the written files # Directory structure: out_dir///data.txt files # Clean up unlink(out_dir, recursive = TRUE) ``` # Write a list of tables to files: `export_list()` ```{r example-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 ) # Returns (invisibly) a named vector of the written file paths files # Clean up unlink(out_dir, recursive = TRUE) ```