03 · Clean + automate · ETL pipeline
Catch the bad rows before the database does.
The VGS Batch Importer turns historical rangeland Excel datasheets into checked SQLite records, with protocol-aware validation and an explicit review step before import.
The problem
Historical spreadsheets carry years of value—and years of inconsistency.
Vegetation records can arrive across many workbooks, protocol versions, species-code conventions, and site names. A direct import is fast only until a malformed row, stale code, or ambiguous location enters the shared database.
The import process needed to handle volume without making it easy to skip quality assurance.
The solution
Separate reading, checking, reviewing, and writing.
The R-based application ingests multiple Excel files, recognizes point ground cover, line intercept, and nested-frequency records, and normalizes them for SQLite.
Before writing, it validates USDA species codes and can use USFS shapefiles to standardize the site hierarchy from region and forest through ranger district, allotment, and pasture. Comparison and correlation reports help reviewers inspect what changed.
Defensible proof
The checks are part of the pipeline, not a promise beside it.
The public repository exposes the importer rather than asking visitors to accept a black-box claim. Its documented workflow covers batch Excel ingestion, USDA species validation, shapefile-based site renaming, automated comparison reports, and three rangeland vegetation protocol families.
Technical details
The application uses R Shiny for the review interface and SQLite for the destination database. The ETL path is organized around protocol-specific parsing, validation reports, normalized site naming, and a deliberate write step.
The repository is the appropriate technical proof for this project; no private operational data or unsupported time-savings claim is presented here.