Exporting Data from Dataverse using PowerShell

Microsoft Dataverse is a cloud-based data platform that integrates seamlessly with the Power Platform. If you need to export data from Dataverse for reporting, backups, or migrations, PowerShell provides an efficient automation method.

This step-by-step guide explains how to export data from a Dataverse table into a CSV or JSON file using PowerShell.

Step 1: Prerequisites

1. Required Permissions

You must have the System Administrator or Power Platform Admin role in Dataverse.
Ensure that the Microsoft Dataverse API is enabled for your environment.

2. Install and Import Required PowerShell Modules

To interact with Dataverse, install and import the necessary 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: Retrieve Data from Dataverse

1. Define the Dataverse Table (Entity) Name

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

2. Retrieve All Records from the Table

# Fetch records from Dataverse
$records = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -AllRecords

# Display retrieved records
$records

This command retrieves all records from the specified Dataverse table.

Step 4: Export Data to a CSV File

1. Define the Export File Path

$csvFilePath = "C:\Dataverse_Export\Customers.csv"

2. Convert Data to CSV Format and Export

# Export data to CSV
$records | Select-Object firstname, lastname, email, phone | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8

Write-Host "Data successfully exported to $csvFilePath"

This exports selected fields from the Dataverse table into a CSV file.

Step 5: Export Data to a JSON File

1. Define the JSON Export File Path

$jsonFilePath = "C:\Dataverse_Export\Customers.json"

2. Convert Data to JSON Format and Export

# Convert data to JSON and export
$records | ConvertTo-Json -Depth 3 | Out-File -FilePath $jsonFilePath -Encoding UTF8

Write-Host "Data successfully exported to $jsonFilePath"

This exports all retrieved records into a JSON file.

Step 6: Filtering Exported Data

Export Only Active Customers

# Filter active customers
$activeCustomers = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -Filter "status eq 'Active'"

# Export to CSV
$activeCustomers | Export-Csv -Path "C:\Dataverse_Export\Active_Customers.csv" -NoTypeInformation -Encoding UTF8

This exports only customers with an “Active” status.

Step 7: Export Data with Pagination

If the Dataverse table contains a large amount of data, pagination ensures that all records are retrieved.

# Retrieve data with pagination
$batchSize = 5000  # Define batch size
$allRecords = @()
$nextPageLink = $null

do {
    # Fetch records in batches
    $pagedRecords = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -TopCount $batchSize -SkipToken $nextPageLink
    $allRecords += $pagedRecords
    $nextPageLink = $pagedRecords.SkipToken
} while ($nextPageLink)

# Export paginated data to CSV
$allRecords | Export-Csv -Path "C:\Dataverse_Export\Paginated_Customers.csv" -NoTypeInformation -Encoding UTF8

Write-Host "Paginated data successfully exported."

Step 8: Verify Exported Data

View CSV Data in PowerShell

# Read CSV data
Import-Csv -Path "C:\Dataverse_Export\Customers.csv"

View JSON Data in PowerShell

# Read JSON data
Get-Content -Path "C:\Dataverse_Export\Customers.json" | ConvertFrom-Json

Step 9: Disconnect from Dataverse

Once the export process is complete, disconnect from Dataverse.

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