Importing Data into Dataverse using PowerShell

Microsoft Dataverse enables secure data storage and management for Power Platform applications. If you need to bulk import data into Dataverse, PowerShell provides a powerful automation method. This guide will explain step by step how to import data into a Dataverse table using PowerShell.

Step 1: Prerequisites

1. Required Permissions

You must have the System Administrator or Power Platform Admin role in Dataverse.
The Microsoft Dataverse API should be enabled for your environment.

2. Install and Import PowerShell Modules

To interact with Dataverse, install and import the required PowerShell modules.

# Install Power Platform Administration module
Install-Module -Name Microsoft.PowerPlatform.Administration -Scope CurrentUser -Force

# Install Dataverse Client module
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import modules
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

Step 2: Connect to Dataverse

Option 1: Interactive Login (Prompt for Credentials)

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

A sign-in window will appear, prompting you to log in with your Microsoft 365 admin credentials.

Option 2: Using Service Principal (App Registration)

For automated scripts, use an Azure AD App Registration.

# Define connection details
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Create authentication context
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Step 3: Prepare Data for Import

The data you want to import should be in a structured format like CSV or JSON.

Example CSV File (Customers.csv)

FirstName,LastName,Email,Phone
John,Doe,john.doe@example.com,123-456-7890
Jane,Smith,jane.smith@example.com,987-654-3210

Save the CSV File

Ensure the CSV file is stored in a known location, e.g., C:\Dataverse_Import\Customers.csv.

Step 4: Read Data from CSV File Using PowerShell

# Define the file path
$csvFilePath = "C:\Dataverse_Import\Customers.csv"

# Import CSV data
$customers = Import-Csv -Path $csvFilePath

# Display imported data
$customers

This will load the CSV data into a PowerShell object.

Step 5: Import Data into Dataverse

1. Define the Dataverse Table (Entity) Name

$tableName = "new_Customers"  # Replace with your table’s logical name

2. Loop Through CSV Data and Insert Records

foreach ($customer in $customers) {
    # Create a new row in Dataverse
    $record = @{
        "firstname" = $customer.FirstName
        "lastname"  = $customer.LastName
        "email"     = $customer.Email
        "phone"     = $customer.Phone
    }

    # Insert the record into Dataverse
    New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields $record

    Write-Host "Inserted: $($customer.FirstName) $($customer.LastName)"
}

Each row from the CSV is converted into a PowerShell object and imported into Dataverse.

Step 6: Import Data from JSON File

If your data is in JSON format, you can load it and import it similarly.

Example JSON File (Customers.json)

[
    { "FirstName": "John", "LastName": "Doe", "Email": "john.doe@example.com", "Phone": "123-456-7890" },
    { "FirstName": "Jane", "LastName": "Smith", "Email": "jane.smith@example.com", "Phone": "987-654-3210" }
]

1. Load JSON Data in PowerShell

# Define the file path
$jsonFilePath = "C:\Dataverse_Import\Customers.json"

# Import JSON data
$jsonData = Get-Content -Path $jsonFilePath | ConvertFrom-Json

# Display imported data
$jsonData

2. Insert JSON Data into Dataverse

powershellCopyEditforeach ($customer in $jsonData) {
    $record = @{
        "firstname" = $customer.FirstName
        "lastname"  = $customer.LastName
        "email"     = $customer.Email
        "phone"     = $customer.Phone
    }

    # Insert the record into Dataverse
    New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields $record

    Write-Host "Inserted: $($customer.FirstName) $($customer.LastName)"
}

Step 7: Verify Imported Data

Once the import process is complete, you can verify the data using PowerShell.

# Retrieve records from Dataverse
$importedRecords = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -TopCount 10

# Display records
$importedRecords

This will fetch the top 10 records from Dataverse and display them.

Step 8: Handle Duplicate Records

To prevent inserting duplicate records, check if a record exists before adding a new one.

foreach ($customer in $customers) {
    # Check if record already exists
    $existingRecord = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -Filter "email eq '$($customer.Email)'"

    if ($existingRecord.Count -eq 0) {
        # Create a new record
        $record = @{
            "firstname" = $customer.FirstName
            "lastname"  = $customer.LastName
            "email"     = $customer.Email
            "phone"     = $customer.Phone
        }

        # Insert the record
        New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields $record

        Write-Host "Inserted: $($customer.FirstName) $($customer.LastName)"
    } else {
        Write-Host "Skipping duplicate: $($customer.FirstName) $($customer.LastName)"
    }
}

Step 9: Disconnect from Dataverse

Once the import process is completed, disconnect from Dataverse.

Disconnect-CdsService -Connection $connection
Write-Host "Disconnected from Dataverse."