Migration from Spreadsheets

Overview

Many teams track positions in Excel or Google Sheets. Migrating that data to DMTR involves exporting from spreadsheets, mapping columns to the DMTR schema, validating data, importing via the API, and verifying the migration.

Export from Excel

Export your spreadsheet to CSV or a structured format (JSON, Parquet). Preserve column headers and data types. Handle merged cells, formulas, and multi-sheet workbooks by flattening to a single table per entity type. Clean obvious errors (e.g., #REF!, empty rows) before export.

Map to DMTR schema

Create a mapping from your columns to DMTR fields. Required fields: entity_id (or generate), status, asset.issuer, asset.vehicle_class, transfer.transferable, core compliance fields. Map optional fields as available. Handle value transformations (e.g., "Yes"/"No" to boolean, date formats).

Validate data

Validate before import: required fields present, enums valid, references resolvable (e.g., issuer IDs exist). Use the validation API or a script. Fix validation errors; consider a staging import to a test environment first. Document mapping decisions for audit.

Import

Import via POST /positions in batch or one-by-one. Use idempotency keys keyed by source row or entity. Handle rate limits with throttling. Log successes and failures for reconciliation. For large datasets, use async batch endpoints if available.

Verify

After import, query the API to verify records. Compare counts, spot-check key fields, and validate relationships. Run reconciliation reports. Address any discrepancies before decommissioning the spreadsheet.