Dataverse data import templates

Bulk-loading data into Dataverse — a list of contacts from a marketing event, the customer master from a legacy system, a one-off catalog refresh — is a common need. The data import wizard in model-driven apps and the Excel templates generated per table are the built-in answer. They're underused, often because users default to ad-hoc connector-based loads or scripts when the built-in tooling is simpler.

The starting point: import template. From any table's view in the model-driven app:

1. Open the table view.
2. Click Export Template for Import (or Excel Templates → Download a Template).
3. An Excel file is generated with all importable columns as headers.
4. Optional rows include sample data and lookups guidance.

The template ensures column names match Dataverse's logical names — much harder to get wrong than freehand.

Filling the template. Users populate rows with data. Notes:

• Text columns — plain text.
• Number columns — numeric values, no formatting.
• Date columns — ISO format (yyyy-MM-dd) or local format consistent with the user's regional settings.
• Choice columns — the choice label (e.g., "Active"), not the numeric value.
• Lookup columns — match by display name of the related record; system finds the record.
• Boolean — Yes/No, True/False.

Import wizard.

1. From the table view, Import from Excel.
2. Pick the populated template.
3. Wizard previews mapping.
4. Adjust mappings if needed (custom columns may need explicit mapping).
5. Configure duplicate detection and owner.
6. Submit.

The import runs as a background system job. Progress and errors are visible in the Imports view.

Lookup resolution. When importing a sales order with customerLookup = "Acme Corp":

• The system searches the related table (Account, typically) for a row named "Acme Corp".
• If exactly one match, the lookup is set.
• If zero matches, the row fails with "Lookup not found."
• If multiple matches, the row fails with "Ambiguous lookup."

For unambiguous lookups, use a unique identifier (alternate key) in the lookup column rather than display name.

Alternate keys. A table can have one or more alternate keys — column combinations marked unique. Imports can reference rows by alternate key, removing display-name ambiguity. Common alternate keys:

• Account: accountnumber.
• Contact: emailaddress1 (sometimes).
• Product: productnumber.

Setting up alternate keys for import-prone tables is a small investment that pays off forever.

Duplicate detection. During import, the system runs configured duplicate detection rules. Options:

• Allow duplicates — bypass; risk of dirty data.
• Block duplicates — fail rows that match existing records.
• Update on match — modern option; update the existing record instead of inserting.

For master data imports, the "update on match" pattern is the right answer — load the file as the source of truth, system reconciles.

Performance. Imports are batched but not super fast:

• Hundreds to a few thousand rows per minute for moderate-complexity tables.
• Plugins and workflows fire per row — heavy custom logic dramatically slows the import.
• Async plugin executions queue up — can affect responsiveness during large imports.

For very large imports (100K+ rows), use Dataverse's batch APIs or Azure Data Factory rather than the import wizard.

Multi-table imports. Importing related records (orders with order lines):

• Import the parent first (orders).
• Import the children second (order lines), referencing the parents by alternate key or display name.

A single ZIP package with multiple Excel sheets, each mapped to a table, can be imported as a coordinated job.

Error handling. Failed rows show in the Failures view of the import job. Each error includes:

• The row number in the source.
• The column that failed.
• A description (lookup not found, validation error, plugin exception).

Iterate: fix the source file, re-import only the failed rows. Don't re-import the whole file unless you also want to update already-successful rows.

Re-importing for updates. Imports can update existing records:

• Map a unique column (primary ID or alternate key) to the row identifier.
• Configure "update on match."
• Subsequent imports update fields the file specifies.

Useful for incremental refreshes — load a delta file each day.

Common pitfalls.

• Wrong locale for dates. Mixed MM/dd/yyyy and dd/MM/yyyy rows; some import wrong without erroring.
• Lookup by name fails on whitespace differences. "Acme Corp." vs "Acme Corp"; alternate keys help.
• Choice value mismatch. Importing "Acitve" vs "Active"; fail.
• Owners not set. Default owner is the importing user; mass imports leave one user owning thousands of records.
• Plugins crashing the import. Heavy synchronous plugin on Create slows imports to a crawl; consider temporarily disabling, importing, re-enabling.
• No backup before import. Botched import overwrites data; no recovery. Always backup or trial in a sandbox.

Bulk delete after a botched import. Bulk delete jobs can remove a recent import's rows; track each import's job ID for clean rollback.

Operational rule. For occasional master data refreshes and one-off loads, the import wizard with Excel templates is the right tool. For ongoing high-volume integrations, dedicated ETL (Dataverse data flows, Azure Data Factory, custom code via Web API) outperforms. Pick the tool that matches the cadence — wizard for ad-hoc, automated pipeline for recurring.