[Update June 2020] There’s a newer post that does the same as this and is more complete -it includes paging and updating records. You might want to check it out here.
If you’ve used Microsoft CRM as a power user (on-premise or online), chances are you’ve come across the standard way of querying CRM data, FetchXml.
You can run this by hand but of course the real power of it is using it to automate tasks. And another great way to automate tasks in Windows is, naturally, powershell.
So here’s a script I’m using to run a fetch xml and export the results to a csv file:
#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, May 2018
#
# Prerequisites:
# 1. Install PS modules
# Run the following in a powershell with admin permissions:
# Install-Module -Name Microsoft.Xrm.Tooling.CrmConnector.PowerShell
# Install-Module -Name Microsoft.Xrm.Data.PowerShell -AllowClobber
#
# 2. Write password file
# Run the following and enter your user's password when prompted:
# Read-Host -assecurestring | convertfrom-securestring | out-file C:\temp\crmcred.pwd
#
# ============ Constants to change ============
$pwdFile = "C:\temp\crmcred.pwd"
$username = "myusername@mycompany.com"
$serverurl = "https://my-crm-instance.crm4.dynamics.com"
$fetchXmlFile = "c:\temp\fetch.xml"
$exportfile = "C:\temp\crm_export.csv"
$exportdelimiter = ";"
# =============================================
# ============ Login to MS CRM ============
$password = get-content $pwdFile | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$password
try
{
$connection = Connect-CRMOnline -Credential $cred -ServerUrl $serverurl
# for on-prem use :
# $connection = Connect-CrmOnPremDiscovery -Credential $cred -ServerUrl $serverurl
# you can also use interactive mode if you get e.g. problems with multi-factor authentication
# $connection = Connect-CrmOnlineDiscovery -InteractiveMode -Credential $cred
# or you can use a connection string if you want to use e.g. OAuth or a Client Secret
# but then the password must be plaintext which is kind of a security no-no
# $connString = "AuthType=ClientSecret;url=$serverurl;ClientId=$username;ClientSecret=$password"
# $connection = Get-CrmConnection -ConnectionString $connString
}
catch
{
Write-Host $_.Exception.Message
exit
}
if($connection.IsReady -ne $True)
{
$errorDescr = $connection.LastCrmError
Write-Host "Connection not established: $errorDescr"
exit
}
else
{
Write-Host "Connection to $($connection.ConnectedOrgFriendlyName) successful"
}
# ============ Fetch data ============
$fetchXml = [xml](Get-Content $fetchXmlFile)
$result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXml.OuterXml
# ============ Write to file ============
# Obviously here, instead of writing to csv directly, you can loop and do whatever suits your needs, e.g. run a db query, call a web service etc etc
$result.CrmRecords | Select -Property lastname, firstname | Export-Csv -Encoding UTF8 -Path $exportfile -NoTypeInformation -Delimiter $exportdelimiter
When you use your own FetchXml, do remember to change the properties in the last line (lastname, firstname).
For a quick test, the example FetchXml I’m using is the following:
<fetch mapping="logical" version="1.0">
<entity name="account">
<attribute name="customertypecode" alias="customertypecode"/>
<attribute name="name" alias="company_name"/>
<attribute name="emailaddress1" alias="company_emailaddress1"/>
<link-entity name="contact" from="accountid" to="accountid" link-type="inner">
<attribute name="lastname" alias="lastname"/>
<attribute name="firstname" alias="firstname"/>
</link-entity>
</entity>
</fetch>
Have fun coding!
Dot Jim 
Getting error that Get-CrmRecordsByFetch is not recognise function in powershell.
How I overcome this issue.
Hi Abh
You probably don’t have the PowerShell cmdlets for Dynamics CRM installed. Go to the “Register the cmdlets” part here:
https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/xrm-tooling/use-powershell-cmdlets-xrm-tooling-connect
Hope this helps
Jim
Actually, maybe here it’s even easier:
https://www.powershellgallery.com/packages/Microsoft.Xrm.Data.Powershell/2.8.1.3
Jim
Hi Abh
I know you’ve solved it by now but just FYI, I updated the script slightly and added the prerequisites in the comments on top.
Jim
in the results.CRM record how do I check if last name is empty and then export the CSV?
Hi Rishav
The easiest and more efficient (reg. network and cpu usage) thing you can do is filter the records already on CRM. That means that the powershell code stays the same but the fetch xml changes. So if you are searching for empty last names, you can add something like:
<link-entity name="contact" from="accountid" to="accountid" link-type="inner"> <attribute name="lastname" alias="lastname"/> <attribute name="firstname" alias="firstname"/> <filter> <condition attribute="lastname" operator="eq" value="null" /> </filter> </link-entity>Note the filter expression, that’s what’s new here.
Hope this helps,
Jim
This looks really good… but I wanted to omit the whole record if the last name is missing and if all the records have missing last name then I should display a message saying “cannot export CSV as last name is missing”
Well, that’s exactly what the fetchxml filter does.
Sure, you can do this by checking the $result.CrmRecords collection, like this:
if ($result.CrmRecords.Count -eq 0) { Write-Host "Cannot export CSV, no records found" }Thanks… this is working,,, awesome 😀
Thank you for this, I’m running into an issue though. When I pull a file using FetchXML if one of the columns in the first record pulled is blank it just skips that column entirely, but I need the data in that column. I’ve been trying to find a solution but nothing seems to work. Do you know a way to force ti to bring in all columns of data regardless of if their empty or not?
Hi Kyle
Hmmm that’s not the behavior I’m getting. In an experiment I just did, if the 1st record is blank (“does not contain data” in advanced find) the rest will get this value -if they contain data of course.
That’s the fecthxml I’m experimenting with (el_businessid = 767 has no data for el_abacusid, so the 1st record has no value for this field):
<fetch version="1.0" mapping="logical" distinct="true" count="50"> <entity name="account"> <attribute name="accountid" /> <attribute name="el_businessid" /> <attribute name="el_abacusid" /> <filter type='and'> <condition attribute='el_businessid' operator='ge' value='767' /> </filter> <order attribute='el_businessid' descending='false' /> </entity> </fetch>And the powershell looks like that:
# code above is the post's code unchanged $result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXml.OuterXml foreach($rec in $result.CrmRecords) { Write-Host "$($rec.el_abacusid.Length)" if (!$rec.el_abacusid) { Write-Host "$($rec.accountid) $($rec.el_businessid) null" } }The output looks like the following:
Can you post a piece of fetchxml and code that you’re using?
I got it working now. Since I have multiple pages of information that I need to pull I was treating the data as pages rather than individual records. I just added that for loop in there and got everything working from there. Thanks.
Hi Kyle
I know it’s been like a year, but I just wrote another article that uses paging 🙂 You can find it here: https://dandraka.com/2020/06/03/powershell-dynamics-crm-get-results-and-update-records-for-large-datasets-with-paging/
Kind regards,
Jim