ABRP Drive Data Ingestion

A Python tool to ingest ABRP (A Better Route Planner) exported XLSX files into SQLite and serve via Datasette for analysis.

Features

• Import ABRP XLSX exports with automatic deduplication
• Handle overlapping data across multiple exports
• SQLite database with computed fields (efficiency, energy consumption)
• Datasette interface with custom queries and visualizations
• CLI tool for easy data management

Installation

This project uses uv for fast Python package management.

1. Install dependencies with uv:

uv sync

This will create a virtual environment and install all dependencies automatically.

Usage

Import Data

Import a single XLSX file:

uv run abrp-ingest import abrp-exports/2025-11-30_to_2025-12-06.xlsx

Import all XLSX files from a directory:

uv run abrp-ingest import-all abrp-exports/

Validate a file without importing:

uv run abrp-ingest validate abrp-exports/2025-11-30_to_2025-12-06.xlsx

View Statistics

uv run abrp-ingest stats

Launch Datasette

./scripts/serve_datasette.sh

Or manually:

uv run datasette data/abrp_drives.db --metadata datasette/metadata.yml --port 8001

Database Schema

Activities Table

Stores all drive and charge activities with:

• Activity type (Drive or Charge)
• Timestamps (start/end)
• Location data (lat/lon/address)
• State of Charge (SoC)
• Distance, energy consumption, efficiency
• Deduplication hash for preventing duplicates

Import History Table

Tracks import operations:

• File name and hash
• Import timestamp
• Records found, imported, skipped

Deduplication Strategy

Since ABRP exports don't include unique IDs, this tool uses:

1. Hash-based deduplication: Creates SHA256 hash from key fields (activity type, times, vehicle, SoC, distance/energy)
2. Database constraint: UNIQUE constraint on dedup_hash prevents duplicates
3. File tracking: Calculates file hash to skip re-importing identical files

This handles overlapping date ranges in exports while avoiding false positives.

Custom Datasette Queries

The tool includes pre-configured SQL queries:

• Daily Summary: Activities, miles, kWh per day
• Weekly Summary: Weekly rollups
• Efficiency Analysis: Driving efficiency over time
• Charge Sessions: Charging power, duration, SoC increase
• Trip Analyzer: Long trips >50 miles

Development

Run tests:

uv run pytest

Format code:

uv run black src/ tests/
uv run ruff check src/ tests/

Technical Notes

XLSX Column Misalignment

ABRP exports have a quirk: Charge records are missing column E (Distance), causing subsequent columns to shift left. The parser handles Drive and Charge rows differently to account for this.

Battery Capacity Estimation

The tool estimates battery capacity from charge sessions (looking for near 0% to 100% charges) to calculate energy consumption for drives.

License

Private project