Data Import and Export Options

Managing Dataverse: Data Import and Export Options

In the world of modern business applications, data is rarely static. It flows between systems, originates from legacy databases, and needs to be extracted for high-level analytics. Microsoft Dataverse, the backbone of the Power Platform, provides a sophisticated set of tools designed to handle these movements. Whether you are migrating thousands of customer records from an old SQL Server, syncing product lists from an Excel sheet, or pushing data out to Azure for machine learning, understanding your import and export options is critical.

This lesson explores the various mechanisms available for moving data into and out of Dataverse. We will move beyond simple "copy and paste" logic and look at how to maintain data integrity, handle complex relationships, and choose the right tool for the specific scale of your project. By the end of this guide, you will be able to architect a data movement strategy that is efficient, secure, and easy to maintain.

Understanding the Dataverse Data Landscape

Before we dive into the "how," we must understand the "what." Dataverse is not just a flat storage system; it is a relational database with built-in logic, security, and validation rules. When you import data, you aren't just filling cells in a table; you are interacting with an API that enforces business rules. If you try to import a contact record that references a company that doesn't exist, Dataverse will stop you—unless you've configured your import to handle those relationships correctly.

Data movement in Dataverse generally falls into three categories:

1. Ad-hoc movements: Small, one-time tasks usually performed by business users using Excel or CSV files.
2. Scheduled integrations: Recurring data syncs from external sources using Power Query or Dataflows.
3. Enterprise-scale pipelines: High-volume data transfers using Azure Data Factory or custom code for millions of records.

Callout: Dataverse is API-First It is important to remember that every data import method mentioned in this lesson—even the simple Excel upload—eventually talks to the Dataverse Web API or the Organization Service. This means that all your plugins, workflows, and validation rules will trigger during an import unless you explicitly design them to behave otherwise. This ensures data consistency but can impact import speed.

Option 1: The Excel and CSV Import Experience

For many users, Microsoft Excel is the most familiar environment for data management. Dataverse provides a native "Get Data from Excel" feature that is perfect for smaller datasets (typically under 5,000 records) or one-time imports.

How it Works

When you use the "Import from Excel" feature in a model-driven app or the Power Apps portal, Dataverse provides a guided wizard. You upload your file, and the system attempts to map your column headers to the logical names of the columns in your Dataverse table.

Key Features

• Automatic Mapping: If your Excel headers match the Display Names of your columns, Dataverse maps them automatically.
• Lookup Resolution: You can map a text string in Excel (like a "Company Name") to a Lookup field in Dataverse. The system will search the target table for a matching record.
• Data Validation: If a column in Dataverse is a "Choice" (dropdown), the import will fail if the Excel value doesn't match one of the predefined options, ensuring you don't end up with "dirty" data.

Step-by-Step: Importing via Excel

1. Navigate to the table where you want to add data in the Power Apps maker portal.
2. Select Import > Import data from Excel.
3. Upload your file. Dataverse will analyze the columns.
4. Review the mapping. If a column says "Not Mapped," click it to manually select the correct destination field.
5. Click Import. You can monitor the progress in the "Track Progress" section, which redirects you to the legacy Data Management settings.

Note: If you are updating existing records rather than creating new ones, ensure your Excel file includes the Primary Key (GUID) or an Alternate Key. This allows Dataverse to perform an "Upsert" (Update if exists, Insert if not).

Option 2: Power Platform Dataflows (Power Query)

Dataflows are the "Swiss Army Knife" of Dataverse data management. Built on the Power Query engine—the same technology used in Excel and Power BI—Dataflows allow you to connect to a vast array of data sources, transform the data, and load it into Dataverse on a schedule.

Why Use Dataflows?

Dataflows are superior to simple Excel imports when the data requires "cleaning" before it enters the system. For example, if your source system stores phone numbers as (555) 123-4567 but your Dataverse logic requires 5551234567, you can use Power Query transformations to strip the formatting during the import process.

Common Data Sources

• SQL Server (On-premises or Azure)
• SharePoint Lists
• Web APIs (JSON/XML)
• Azure Blob Storage
• Salesforce and other SaaS platforms

Practical Example: Transforming Data

Imagine you are importing a list of employees. The source file has a column called FullName, but your Dataverse table has FirstName and LastName. In a Dataflow, you can use the "Split Column by Delimiter" transformation to break the name apart before it ever hits the database. This saves you from having to write custom code or manual cleanup scripts.

Tip: Always use "Incremental Refresh" for large datasets in Dataflows. Instead of reloading 100,000 rows every day, Incremental Refresh only pulls records that have been modified since the last run, significantly reducing the load on both your source system and Dataverse.

Option 3: Azure Data Factory (ADF)

When you move into the territory of millions of records, or when you need to orchestrate complex movements across multiple enterprise systems, Azure Data Factory is the tool of choice. ADF is a cloud-based data integration service that allows you to create data-driven workflows for orchestrating and automating data movement and data transformation.

High-Volume Performance

ADF uses the Dataverse connector, which is optimized for the "Batch" API. While an Excel import might process 10 records per second, a well-configured ADF pipeline can process thousands of records per second by utilizing parallel processing and multiple service principals.

Use Case: Legacy Migration

If a company is moving from an on-premises Dynamics CRM 2011 instance to the cloud, they might have 50GB of data. Using Excel or simple Dataflows would be too slow and prone to timeouts. An ADF pipeline can be scheduled to run overnight, handling the complex dependencies (e.g., importing Accounts before Contacts, and Contacts before Cases) while logging every success and failure to a centralized dashboard.

Option 4: Programmatic Import (Web API and SDK)

For developers, the most flexible way to move data is through the Dataverse Web API or the .NET Organization Service. This is necessary when data needs to be moved based on specific application events or when you need a level of logic that Dataflows cannot provide.

The Web API (RESTful)

The Web API is accessible from any language that supports HTTP requests. It follows the OData v4 protocol.

Example: Creating a Record via JavaScript

async function createAccount() {
    const data = {
        "name": "Contoso Ltd",
        "revenue": 500000,
        "description": "A leading provider of tech services."
    };

    const response = await fetch("https://yourorg.api.crm.dynamics.com/api/data/v9.2/accounts", {
        method: "POST",
        headers: {
            "Content-Type": "application/json",
            "OData-MaxVersion": "4.0",
            "OData-Version": "4.0",
            "Authorization": "Bearer [YOUR_ACCESS_TOKEN]"
        },
        body: JSON.stringify(data)
    });

    if (response.ok) {
        const result = await response.json();
        console.log("Account created with ID:", result.accountid);
    }
}

The Organization Service (.NET)

If you are building a custom integration in C#, the CrmServiceClient or the newer ServiceClient provides a strongly-typed way to interact with data. It handles authentication and connection pooling more efficiently than raw HTTP calls.

Example: Using the SDK for Bulk Operations

var request = new CreateMultipleRequest
{
    Targets = new EntityCollection(new List<Entity> {
        new Entity("account") { ["name"] = "Account 1" },
        new Entity("account") { ["name"] = "Account 2" }
    })
};
service.Execute(request);

Callout: Create vs. CreateMultiple In the past, developers had to loop through records and call Create one by one. This was slow because each call had a network overhead. Microsoft recently introduced CreateMultiple and UpdateMultiple messages. These allow you to send a large batch of records in a single network request, which the Dataverse engine processes much faster on the server side.

Exporting Data from Dataverse

Getting data out of Dataverse is just as important as getting it in. Whether for reporting, backup, or integration with other tools, you have several primary options.

1. Export to Excel (Static vs. Dynamic)

When you view a list of records in a Power App, you see an "Export to Excel" button. You have two main choices:

• Static Worksheet: A snapshot in time. Great for sending a quick list to a manager.
• Dynamic Worksheet/PivotTable: This creates a file with a live link back to Dataverse. When you open the file and click "Refresh," it pulls the latest data from the server. This is excellent for recurring financial reports.

2. Azure Synapse Link for Dataverse

This is the modern standard for large-scale data export and analytics. Azure Synapse Link continuously exports your Dataverse data to Azure Data Lake Storage Gen2 or Azure Synapse Analytics.

• No Performance Impact: Unlike running a massive report inside Dataverse, Synapse Link works in the background. It doesn't consume your API limits or slow down the user interface.
• Near Real-Time: Changes in Dataverse are reflected in the Data Lake within minutes.
• Big Data Ready: Once the data is in the Data Lake, you can use Power BI, Spark, or SQL Serverless to analyze millions of rows of data without affecting your production environment.

3. Data Export Service (Legacy)

Warning: The Data Export Service (DES) is being deprecated. If you are currently using it to sync Dataverse to an Azure SQL Database, you should plan a migration to Azure Synapse Link as soon as possible.

Comparison of Data Movement Tools

Best Practices for Data Import

To ensure your data import is successful and doesn't disrupt your environment, follow these industry-standard practices.

1. Use Alternate Keys for Upserts

By default, Dataverse uses a GUID (Global Unique Identifier) as the primary key. When importing data from an external system (like an ERP), you likely have a "Customer Number." By defining that Customer Number as an Alternate Key in Dataverse, you can tell the import engine: "If you see a record with Customer Number 12345, update it. If not, create a new one." This prevents duplicate records.

2. Disable Non-Essential Logic

If you are importing 100,000 records, and you have a synchronous plugin that runs on every "Create" to send a welcome email, your import will take forever and you will spam your customers.

• Temporarily disable plugins and workflows.
• Use the BypassCustomPluginExecution header if using the API.
• Consider running imports during off-peak hours.

3. Validate Data Types Before Import

Dataverse is strict. If a column is a "Whole Number," and your Excel sheet has "1,000.50," the row will fail. Always perform a "Type Check" in your Dataflow or staging area to ensure the data matches the destination schema exactly.

4. Handle Lookups with Care

When importing a Contact and linking it to an Account, the Account must exist first. If you are doing a bulk migration, always import the "Parent" tables before the "Child" tables. If you are using the Web API, you use the @odata.bind syntax to link records using their IDs or Alternate Keys.

Common Pitfalls and How to Avoid Them

Even seasoned consultants run into issues with data movement. Here are the most common traps.

The "Choice" Column Value Trap

In Dataverse, a Choice (Option Set) has a Label (e.g., "Active") and a Value (e.g., 100,000,001). Many beginners try to import the Label. While the Excel wizard can sometimes resolve this, the API and Dataflows usually require the integer Value. If your import is failing with "Option Set value out of range," check that you aren't trying to push text into a number field.

API Throttling and Service Limits

Dataverse has "Service Protection Limits." If you send too many requests in a short period, the server will return a 429 Too Many Requests error.

• The Fix: Implement "Retry Logic" in your code. If you get a 429, wait for the duration specified in the Retry-After header before trying again. If using ADF or Dataflows, these tools handle most of this logic for you automatically.

Ownership and Business Units

Every record in Dataverse must be owned by a User or a Team. If you don't specify an owner during import, the person running the import becomes the owner. In many cases, this isn't what you want. Ensure your import mapping includes the Owner field, mapped to the email address or SystemUserID of the correct user.

Formatting of GUIDs

When using the Web API to link records, the GUID must be formatted correctly (e.g., bb67688c-74b1-ed11-83ff-000d3a310c3a). If you miss a hyphen or include extra spaces, the API will return a "Bad Request" error.

Advanced Scenario: Using the Dataverse Web API for Bulk Updates

Let's look at a more complex example. Suppose you need to update the "Credit Limit" for 500 accounts based on a calculation performed in an external Python script. Using the $batch endpoint is the most efficient way to do this.

A batch request allows you to group multiple operations into a single HTTP request. This is much faster than sending 500 individual PATCH requests.

Example: Batch Request Structure

POST [Organization URI]/api/data/v9.2/$batch HTTP/1.1
Content-Type: multipart/mixed; boundary=batch_sample_boundary
Accept: application/json

--batch_sample_boundary
Content-Type: application/http
Content-Transfer-Encoding: binary

PATCH accounts(bb67688c-74b1-ed11-83ff-000d3a310c3a) HTTP/1.1
Content-Type: application/json

{
    "creditlimit": 25000
}

--batch_sample_boundary
Content-Type: application/http
Content-Transfer-Encoding: binary

PATCH accounts(cc78799d-85c2-fe22-94gg-111e4b421d4b) HTTP/1.1
Content-Type: application/json

{
    "creditlimit": 50000
}

--batch_sample_boundary--

In this example, we are updating two different account records in one go. The boundary string separates the individual operations. This reduces the number of round-trips between your script and the Dataverse server.

Step-by-Step: Setting Up Azure Synapse Link

Since Synapse Link is the recommended path for data export and analytics, let's walk through the setup process.

1. Prerequisites: You need an Azure Subscription and an Azure Data Lake Storage Gen2 account in the same region as your Dataverse environment.
2. Enable the Link:
   • Go to the Power Apps maker portal.
   • Select Azure Synapse Link in the left-hand menu.
   • Click New link.
   • Select "Connect to your Azure Synapse Analytics workspace" or "Connect to your Azure Data Lake Storage Gen2."
3. Select Tables:
   • Choose the tables you want to export (e.g., Account, Contact, Opportunity).
   • Note that tables must have "Track Changes" enabled in their properties to be eligible for Synapse Link.
4. Configure Options:
   • You can choose to include "Metadata" (which includes the labels for your choice columns, making reporting much easier).
   • Select the "Spark" or "SQL" configuration if you want to use Synapse Analytics directly.
5. Monitor the Initial Sync:
   • Dataverse will perform an initial "Snapshot" of your data. Depending on the size, this could take minutes or hours.
   • Once the status changes to "Active," any new changes in Dataverse will automatically be pushed to the lake.

Data Integration FAQs

Q: Can I import data into a "ReadOnly" field? A: No. Fields like CreatedOn, ModifiedOn, and Calculated columns cannot be written to directly. However, if you are doing a migration and need to preserve the original "Created On" date, you can map your source date to the overriddencreatedon field. Dataverse will then use that value for the CreatedOn field for that record.

Q: What happens if my import file has duplicate rows? A: If you haven't defined "Duplicate Detection Rules" or "Alternate Keys," Dataverse will happily import both rows, creating duplicates. It is always better to clean your data before it leaves the source or during the Dataflow transformation stage.

Q: Is there a limit to how many Dataflows I can have? A: While there isn't a hard limit on the number of Dataflows, there are limits on the total execution time and concurrent runs per environment. For massive enterprises, it's better to consolidate logic into fewer, more efficient Dataflows rather than hundreds of tiny ones.

Q: How do I handle multi-select choices? A: Multi-select choices are stored as comma-separated strings of the integer values (e.g., 100001,100005). When importing, ensure your source data matches this format.

Key Takeaways

1. Choose the Right Tool for the Job: Use Excel for small ad-hoc tasks, Dataflows for regular cleaning and syncing, and Azure Data Factory or the API for high-volume enterprise migrations.
2. Prioritize Data Integrity: Always use Alternate Keys to handle "Upserts." This prevents duplicate records and makes it easier to link data from external systems.
3. Mind the API Limits: Dataverse is a shared resource. Respect service protection limits by using batching (CreateMultiple) and implementing retry logic in custom code.
4. Prepare for Growth with Synapse Link: Don't rely on legacy tools like the Data Export Service. Move your analytics and reporting workflows to Azure Synapse Link for better performance and scalability.
5. Cleanse Data Early: Use the Power Query engine within Dataflows to transform, split, and format data before it reaches Dataverse. This keeps your database clean and your business logic simple.
6. Sequence Matters: When importing relational data, always import the "one" side of the one-to-many relationship before the "many" side (e.g., Accounts before Contacts).
7. Monitor and Audit: Use the "Data Management" area in the Power Platform Admin Center to track the success and failure of imports, and always review the error logs to understand why specific rows failed.