Generate CSV from Dynamics 365 with Custom Headers

In my previous post we retrieved account records from Dynamics 365 (CRM) and exported those to a CSV-file. In this post we will expand the example with a transformation function to get nicer property names and CSV headers.

The Export-CSV cmdlet exports all the attributes on the objects piped to it. In our last example we used Select-Object to only include the properties we wanted. These properties are still named by the original object. In most cases this is not desirable. You could just replace the CSV file header row, but a cleaner way would be to transform the object somehow. One way to achieve this is to use Select-Object as mentioned here.

Using our example from before:

1
2
3
4
5
6
7
8
9
10
(Get-CrmRecords -EntityLogicalName account -Fields "name","telephone1").CrmRecords |
Select-Object @{
        N="AccountName"
        E={$_.name}
    },
    @{
        N="Phonenumber"
        E={$_.telephone1}
    } |
Export-Csv -Path "accounts.csv"

This is a nice trick and certainly very flexible. You can select any property on the source object and assign it to a new property. But what if you want to parametrize and use a mapping table for the transformation? You could probably cook-up a function that provides a mapping array for Select-Object. But since I only just came up with that and it actually sounds a bit complicated, I propose a different approach.

Let’s create an advanced function to do the heavy lifting for us:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
function ConvertTo-CustomObject{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
        $SourceObject,
        [Parameter(Mandatory=$True)]
        [hashtable]$MappingTable
    )
    PROCESS {
        $Property = @{}
        $MappingTable.Keys | ForEach-Object {
            $Property.Add(
                $MappingTable[$_],
                $SourceObject.$_
            )
        }
        New-Object psobject -Property $Property
    }
}

The function ConvertTo-CustomObject accepts objects from the pipeline and a mapping table. It then uses the mapping table to iterate across the the properties we want adding them to a property hashtable. Lastly it creates a new object using the properties and writes it to the output.

So again, using our example, we create a mapping table like so:

1
2
3
4
$FieldsMappings = @{
    "name" = "AccountName"
    "telephone1" = "Phone"
}

And then we can replace the Select-Object part with our function:

1
2
3
(Get-CrmRecords -EntityLogicalName account -Fields $FieldsMappings.Keys -AllRows).CrmRecords |
    ConvertTo-CustomObject -MappingTable $FieldsMappings |
    Export-Csv -Path "account.csv"

We retrieve the attributes we want from the system (the keys of the $Fieldmappings hashtable), pipe the objects to our function and write the resulting objects to a CSV like before, but with a nicer header row. Complete code here.

While this may seem like a complex way to do it, using objects gets us some nice benefits. Eg. try replacing the Export-CSV line with Out-Gridview and see that happens. If you would like to include more attributes, all you need to is add items to the mapping table.