Export Data from Dynamics 365 using PowerShell

Did you know that you can manage your Dynamics 365 instance using your favorite automation tool PowerShell? The Microsoft.Xrm.Data.PowerShell-module lets you manage your solutions and such but also has functionality to work on records. In this post I am going to show you how to use the module for data export.

The first thing you need to do is install the module on your system. Just run the command Install-Module Microsoft.Xrm.Data.PowerShell in PowerShell to get it. Next you need to connect to your instance. There are a few alternatives here so please refer to the project’s GitHub-page. With the Microsoft hosted Dynamics 365 you can use Connect-CrmOnlineDiscovery -InteractiveModeto connect using a GUI.

Working with data

Once connected you can try querying for some data. The command Get-CrmRecords -EntityLogicalName account gets you all (or the first 5000) accounts in your system. The output looks something like this:

1
2
3
4
5
6
7
Key          Value
---          -----
CrmRecords   {@{accountid=f39551c1-81ff-e611-80f3-5065f38a5a01; accountid_Property=[ac...
Count        349
PagingCookie <cookie page="1"><accountid last="{9AD008BF-8A34-E711-80FA-5065F38BC5C1}"...
NextPage     False
FetchXml         <fetch version="1.0" output-format="xml-platform" mapping="logical" d...

As you can see the result is an object with a few different properties. The CrmRecords property contains the actual result of the query. So if you want to output all the accounts you would run (Get-CrmRecords -EntityLogicalName account).CrmRecords. That gets you something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
accountid                 : f39551c1-81ff-e611-80f3-5065f38a5a01
accountid_Property        : [accountid, f39551c1-81ff-e611-80f3-5065f38a5a01]
ReturnProperty_EntityName : account
ReturnProperty_Id         : f39551c1-81ff-e611-80f3-5065f38a5a01
original                  : {[accountid_Property, [accountid, f39551c1-81ff-e611-80f3-506
                            5f38a5a01]], [accountid, f39551c1-81ff-e611-80f3-5065f38a5a01
                            ], [ReturnProperty_EntityName, account], [ReturnProperty_Id ,
                             f39551c1-81ff-e611-80f3-5065f38a5a01]}
logicalname               : account
accountid_Property        : [accountid, b902322e-6005-e711-80f4-5065f38a5a01]
accountid                 : b902322e-6005-e711-80f4-5065f38a5a01
ReturnProperty_EntityName : account
ReturnProperty_Id         : b902322e-6005-e711-80f4-5065f38a5a01
original                  : {[accountid_Property, [accountid, b902322e-6005-e711-80f4-506
                            5f38a5a01]], [accountid, b902322e-6005-e711-80f4-5065f38a5a01
                            ], [ReturnProperty_EntityName, account], [ReturnProperty_Id ,
                             b902322e-6005-e711-80f4-5065f38a5a01]}
logicalname               : account

As you can see there doesn’t seem to be any useful data other than the Id to work with. That’s because we haven’t requested any attributes. To include the account name you need to use the -Fields parameter. Eg. Get-CrmRecords -EntityLogicalName account -Fields "name" includes the account name in the result like so:

1
2
3
4
5
6
7
8
9
10
11
name                      : Test account
name_Property             : [name, Test account]
accountid_Property        : [accountid, f39551c1-81ff-e611-80f3-5065f38a5a01]
accountid                 : f39551c1-81ff-e611-80f3-5065f38a5a01
ReturnProperty_EntityName : account
ReturnProperty_Id         : f39551c1-81ff-e611-80f3-5065f38a5a01
original                  : {[name_Property, [name, Test account]], [name, Test
                            account], [accountid_Property, [accountid, f39551c1-81ff
                            -e611-80f3-5065f38a5a01]], [accountid, f39551c1-81ff-e611-80f
                            3-5065f38a5a01]...}
logicalname               : account

So now that we know how to get data from the system, let’s get it into a CSV-file. You can’t quite do a Get-CrmRecords | Export-CSV, but close to it. Here’s a code snippet:

1
2
3
(Get-CrmRecords -EntityLogicalName account -Fields "name","telephone1").CrmRecords |
    Select-Object -Property "name","telephone1" |
    Export-Csv -Path "accounts.csv"

The code fetches all accounts from the system, selects just the properties we want and finally exports the result to a CSV-file. You might want to use the -NoTypeInformation, -Delimeter and -Encoding parameters. You can see the final code with some improvements here.

In my next post we will improve on this by supplying our own column names instead of the schema names provided by the system.