Updating existing data in bulk in a Microsoft Dynamics CRM 2015 environment is a non-trivial task. I’ll show how you can use MS CRM’s webservice in combination with Powershell to perform the updates, with the data to be updated coming from a csv file.

Introduction

The other day, a colleague asked me for help with updating data in bulk in a Microsoft Dynamics CRM 2015 environment. As it turns out, CRM is really good at importing new records (just use the “Import data” wizard in combination with a regular Excel or .csv file), but this option cannot be used to update existing records with new values. Why not guys?

Apparently, the way to do a bulk update is to first export the existing data with the “Export Data to Excel” action, and opt to “Make this data available for re-importing by including required column headings”. This will generate an Excel workbook with the required identifying information for each record hidden in the first 3 columns. When this file is imported by the Data Import wizard, it will update the records for you.

Obviously the downside to this is that you can’t just use any Excel export that a user gives you, the modifications have to be done in such a specially generated file. Also, as you can tell from the screenshot, this special export format can only be selected for the records on the current page (probably because the generated Excel workbook is full of validation rules which makes it s.l.o.w…). Hence, unless you can get the records that you want to modify to fit on the same page, you can’t use this method.

Luckily, MS CRM 2015 provides an excellent API through its webservice, and I’ll show how to leverage it in combination with Powershell to perform the bulk data update.

I’m using Microsoft Dynamics CRM 2015 (v7.0) in an Internet Facing Deployment (aka IFD), although on-premise works exactly the same, and Powershell 4.

Connecting to MS CRM

We’re going to use the webservice proxy that MS CRM provides as part of its SDK. Copy the Microsoft.Crm.Sdk.Proxy.dll and Microsoft.Xrm.Sdk.dll assemblies to your current working folder, and load them into your Powershell session, along with System.ServiceModel for the authorization classes:

[void][System.Reflection.Assembly]::LoadFile("$pwdmicrosoft.xrm.sdk.dll")
[void][System.Reflection.Assembly]::LoadFile("$pwdmicrosoft.crm.sdk.proxy.dll")
[void][System.Reflection.Assembly]::LoadWithPartialName("system.servicemodel")

To determine where we should connect to, in CRM navigate to Settings, Customizations, Developer resources and copy its Organization Service SOAP endpoint url:

For an IFD scenario you should specify your username and password in the ClientCredentials, whereas in an on-premise scenario you don’t need to because it will default to your Windows credentials.
This is all that’s needed for an OrganizationServiceProxy to connect to CRM:

$crmServiceUrl = "https://myOrg.myCrmProvider.net/XRMServices/2011/Organization.svc"
$clientCredentials = new-object System.ServiceModel.Description.ClientCredentials
$clientCredentials.UserName.UserName = 'Leon@myCrmProvider.net'
$clientCredentials.UserName.Password = 'Secret123'
$service = new-object Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy($crmServiceUrl, $null, $clientCredentials, $null)
$service.Timeout = new-object System.Timespan(0, 10, 0)

To test the connection, try asking who you are:

$request = new-object Microsoft.Crm.Sdk.Messages.WhoAmIRequest
$service.Execute($request)

It should respond with something like this:

UserId         : 0dc96a70-7d2f-e511-80d0-001dd8b721fe
BusinessUnitId : a6099f28-772f-e511-80d0-001dd8b721fe
OrganizationId : 0d67a31e-772f-e511-80d0-001dd8b721fe
ResponseName   : WhoAmI
Results        : {[UserId, 0dc96a70-7d2f-e511-80d0-001dd8b721fe], [BusinessUnitId, a6099f28-772f-e511-80d0-001dd8b721fe], [OrganizationId, 0d67a31e-772f-e511-80d0-001dd8b721fe]}
ExtensionData  : System.Runtime.Serialization.ExtensionDataObject

Loading the data to update from a .csv file

Because both Excel and Powershell can handle comma-separated (.csv) files it makes sense to provide the data to be updated in this way. Suppose that we have several existing Accounts in CRM that we want to update, so our accounts.csv looks like this:

accountid;name;address1_city;telephone1;websiteurl;preferredcontactmethodcode
7e6e0424-0d32-e511-80d1-001dd8b721fe;Alfa;Veenendaal;(555) 123456;http://localhost/alfa;Email
1838263f-0d32-e511-80d1-001dd8b721fe;Beta;Arnhem;(555) 234567;http://localhost/beta;Phone
59a45311-0e32-e511-80d1-001dd8b721fe;Delta;Amsterdam;(555) 345678;http://localhost/delta;

Importing it with Import-Csv produces a PSCustomObject for each line beyond the header, with a string property for each column:

$accounts = Import-Csv "Accounts.csv" -Delimiter ";"
$accounts | Format-Table

Produces:

accountid                                 name       address1_city  telephone1     websiteurl              preferredcontactmethodcode
---------                                 ----       -------------  ----------     ----------              --------------------------
7e6e0424-0d32-e511-80d1-001dd8b721fe      Alfa       Veenendaal     (555) 123456   http://localhost/alfa   Email
1838263f-0d32-e511-80d1-001dd8b721fe      Beta       Arnhem         (555) 234567   http://localhost/beta   Phone
59a45311-0e32-e511-80d1-001dd8b721fe      Delta      Amsterdam      (555) 345678   http://localhost/delta

Performing the bulk update

Updating a record is simply a matter of preparing a new Entity with the Id of the record to be updated. Only the Attributes that we assign a value will be updated in CRM. You can view the Entity definition (via Settings, Customizations, Customize the system) or use the EntityMetadata.xlsx (part of the CRM SDK) to determine which attribute names are available for the Account entity. Finally, $service.Update() takes care of updating the data in CRM:

$accounts | Foreach-Object {
    $entity = New-Object Microsoft.Xrm.Sdk.Entity("account")
    $entity.Id = [Guid]::Parse($_.accountid)
    $entity.Attributes["address1_city"] = $_.address1_city
    $entity.Attributes["telephone1"] = $_.telephone1
    $entity.Attributes["websiteurl"] = $_.websiteurl

    Write-Output ('Updating "{0}" (Id = {1})...' -f $_.name, $entity.Id)
    $service.Update($entity)
}

Note that there also exists an ExecuteMultipleRequest that can be used to send multiple updates in one call.

Working without a known AccountId

In the previous example our .csv file included the accountid of the record to be updated. However, users rarely provide this column, also because there’s not really a way to include the accountid when exporting a view to Excel. But there’s a way to use other information, such as the Account’s name and city, to identify a record (assuming the combination is unique), and to look up the corresponding accountid in CRM. First, we’ll query for all existing Accounts:

$query = new-object Microsoft.Xrm.Sdk.Query.QueryExpression("account")
$query.ColumnSet = new-object Microsoft.Xrm.Sdk.Query.ColumnSet("name", "address1_city")

# RetrieveMultiple returns a maximum of 5000 records by default. 
# If you need more, use the response's PagingCookie.
$response = $service.RetrieveMultiple($query)

Next, we’ll use it to create a (case-insensitive) dictionary that maps each Account’s (name, city) combination to its corresponding accountid:

$idmap = @{ }
$response.Entities | ForEach-Object {
    $key = ("{0}|{1}" -f @($_.Attributes["name"], $_.Attributes["address1_city"]))
    $idmap.Add($key, $_.Id)
}

Once we have this $idmap, performing the bulk update without knowing the accountid becomes:

$accounts | Foreach-Object {
    # Lookup the accountid based on the Account's name and city.
    $key = ("{0}|{1}" -f @($_.name, $_.address1_city))
    $id = $idmap[$key]
    
    if($id)
    {
        $entity = New-Object Microsoft.Xrm.Sdk.Entity("account")
        $entity.Id = $id
        $entity.Attributes["telephone1"] = $_.telephone1
        $entity.Attributes["websiteurl"] = $_.websiteurl

        Write-Output ('Updating "{0}" (Id = {1})...' -f $_.name, $entity.Id)
        $service.Update($entity)
    }
    else
    {
        Write-Output ('Couldn''t determine id for "{0}" in "{1}", skipping it...' -f $_.name, $_.address1_city)
    }
}

Updating OptionSet attributes

As you might have noticed, although the Account.csv includes a preferredcontactmethodcode column, we haven’t used it in our update. This is because this attribute is defined as an “option set” (or “picklist”), which is basically CRM’s version of an enumeration. And you can’t just assign an attribute of type OptionSet a string value, because CRM will then complain about “Incorrect attribute value type System.String”.

Hence, we’ll have to map each string value to its corresponding numerical id ourselves. Luckily, this mapping can be requested from CRM using the RetrieveAttributeRequest, and its result just needs a little massaging to be practical for Powershell:

# For the requested OptionSet attribute, returns a dictionary that maps each string 
# value to its corresponding numerical id.
function GetOptionSet([string]$entityName, [string]$attributeName)
{
    # Request the attribute metadata
    $metadataRequest = New-Object Microsoft.Xrm.Sdk.Messages.RetrieveAttributeRequest
    $metadataRequest.EntityLogicalName = $entityName
    $metadataRequest.LogicalName = $attributeName
    $metadataRequest.RetrieveAsIfPublished = $true
    $metadataResponse = $service.Execute($metadataRequest)

    # If this was indeed a picklist, $metadataResponse.AttributeMetadata is of type 
    # PicklistAttributeMetadata which contains all available options.
    $dict = @{ };
    $metadataResponse.AttributeMetadata.OptionSet.Options | ForEach-Object {
        $label = $_.Label.UserLocalizedLabel.Label
        $value = $_.Value
        $dict.Add($label, $value)
    }

    # Add an empty string that maps to the default value.
    $dict.Add("", $metadataResponse.AttributeMetadata.DefaultFormValue)

    return $dict
}

If we use this function to retrieve the OptionSet values for preferredcontactmethodcode…

$contactmethods = GetOptionSet "account" "preferredcontactmethodcode"
$contactmethods

…it produces:

Name               Value
----               -----
Any                1    
Email              2    
Mail               5    
Fax                4    
Phone              3    
                   1    

With these $contactMethods, we can also update the Account’s preferredcontactmethodcode field by specifying it as an OptionSetValue:

# ...
$entity = New-Object Microsoft.Xrm.Sdk.Entity("account")
$entity.Id = $id
$entity.Attributes["telephone1"] = $_.telephone1
$entity.Attributes["websiteurl"] = $_.websiteurl

# Map the code (e.g. "Mail") to its id (5) and create an OptionSetValue for it
$contactMethodId = $contactMethods[$_.preferredcontactmethodcode]
$contactMethodOption = new-object Microsoft.Xrm.Sdk.OptionSetValue($contactMethodId)

# Note that we need the cast - because we're assigning to a dictionary of 
# String to Object, by default Powershell will want to store the 
# $contactMethodOption as a PSObject which the CRM webservice won't be able 
# to deserialize.
$entity.Attributes["preferredcontactmethodcode"] = [Microsoft.Xrm.Sdk.OptionSetValue]$contactMethodOption

Write-Output ('Updating "{0}" (Id = {1})...' -f $_.name, $entity.Id)
$service.Update($entity)

And indeed, running the combined script will update the records in CRM:

Summary

Until MS CRM provides a better way, using Powershell for a bulk update isn’t too difficult. Obviously what I’ve shown here are just the basics; you can use the MS CRM webservice from Powershell to do pretty much anything (see the list of available messages).

The sky’s the limit 🙂