This R Shiny application was built as an ETL pipeline to ingest batch historical excel datasheets into a SQLite database. Generic Data Sheets were build with QA validations built in. This example allows for site, point ground cover, line intercept, and nested frequency data for multiple transects to be ingested for multiple sheets at a time.
This app is specifically set to connect to a local VGS database. It can be adapted to be used for a variety of different digital data sheets that contain historical data in different formats. Basic structure works like so; reads data, parses data based on keys set in script (e.g., "SiteID" to find site name) and selected inputs from UI, organizes data, connects to local SQL database, creates and executes SQL insert statements to create site, site metadata, locations, protocols for event data (includes event groups and events in each eventgroup), then inserts sample data into each event. Various QA/QC checks are built in to prevent corrupt data from being inserted. Code can be seen at https://github.com/tgilbert14/VGS-Batch-Importer-App
The import engine's sync bookkeeping changed fundamentally. If an import suddenly behaves differently than you remember, this is why — and here is the escape hatch:
- What changed: imports used to stamp a hardcoded
SyncKey = 33(which silently stranded them below the sync watermark — they never uploaded to the portal). Now every run mints a fresh key fromSyncTracking, runs in one transaction, and takes a file backup first. Power Mode no longer writes anything (true dry run). The post-import site merge is scoped to the run's own sites and tombstones its deletes. - If something looks wrong: the pre-import database backup is at
C:/ProgramData/VGSData/backups/VGS50_preimport_<timestamp>.db— copy it back overVGS50.db(with VGS 5 closed) to undo an import completely. - To run the OLD app behavior:
git checkout pre-hardening(a tag preserving the pre-hardening code, including its bugs). The hardening landed in commit7b2538b;git revertindividual pieces from there if a targeted fix is needed. - Old stranded imports (done before this fix, stuck at SyncKey 33): repair with Functions/locatorSyncState_update.R — set
repair_stranded_imports <- TRUEand source it.
Every import now runs through a safety seam (Functions/db_safety.R):
- Backup first: a timestamped copy of
VGS50.dbis written toProgramData/VGSData/backupsbefore any write. - One transaction per import: any error rolls the database back to exactly its pre-import state — no more partial site/protocol trees, and no more "re-run in a clean database" recovery workflow.
- Correct sync stamping: every imported row is stamped with a fresh
SyncKeyminted fromSyncTracking.LastTransaction(the old hardcodedSyncKey = 33silently stranded whole imports below the sync watermark — they never uploaded to the portal). Old stranded imports can be repaired with the opt-in tool in Functions/locatorSyncState_update.R. - Tombstoned deletes: site merges now write
TombStonerows so deletions propagate to the server, and every post-import update is scoped to the rows this import created. - Power Mode = true dry run: the full import + validation executes, then the whole transaction is rolled back. Nothing is committed; the log and QA/QC workbook capture every error.
- Survey import is gated off until its answer (
InquiryDatum) writer exists — it used to insert empty scaffolds.
Smoke tests: Rscript tests/test-db-safety.R (14 checks, runs against a throwaway fixture — never the real database).
The UI also got a field-archaeology refresh (topo-lined hero, notebook sidebar, terminal-style import log, and a safety-net footer) — fully offline-safe: sweetalert2 is vendored in www/vendor/ instead of loading from a CDN, which used to silently break every mode toggle on offline/CDN-blocked field laptops. The shrimp button remains, and now wiggles.
The app interface has options to select protocols available for ingest. It has various options depending on the import:
-
Power Mode: This check box bypasses errors by generating and opening a excel workbook to review. The errors are then fixed and eventually the import happens with this setting turned off.
-
Species Replace: This check box enables a SpeciesReplace.xlsx file that can be used to mass update species codes for every file instead of going into each individual file and changing it individually. This is exceptionally helpful when a USDA code has had an update or a client uses the wrong code consistency.
-
Select Protocol for Import: Sampling protocols (quanitative data collection through VGS) are hard-coded for the selection drop down list. Surveys (qualative data collection through VGS) were designed later and query the local VGS database on your local machine to offer drop down selections for what surveys are available on the device to import data to.
-
Batch Import Data: This prompts a pop up window to select the batch import files to import (multiple .xlsx files can be imported at once).
-
Species Count: This button queries that VGS database and counts the species at each site to provide an overview of the data that was collected.
-
Species Check: This button brings a pop up that lets the client select a list of states that the sites are located. It then compares the plant codes in the database to USDA plant lists by each state (www/sp_lists_USDA) to check for inconsistencies and data entry errors.
-
Update Site Name: This links to a script that looks through all location lat/long coordinates and check them in a USFS enterprise shape file to help predict what folder (Allotment/Pasture) they belong in and rename them according to USFS naming conventions (numeric based off of Region-Forest-Ranger District-Allotment-Pasture-SiteID).
-
Survey Log Input?: This new feature offers survey (qualitative data) import with pre-built surveys. This section is still under development.
-
🦐: This is a help button that provides general workflow for batch importing data with this app.
This app also generates a log text file stored in the 'www/' folder to track code flow and debug import issues. ⏬
Contact me @ tsgilbert@arizona.edu for questions, feedback, suggestions, or if you want to collaborate!