PowerShell advanced function (cmdlet) to export CSV and serialize (join) all arrays and collections in input object

There are situations where you need to export object into CSV but the object has for example array or list (collection) of strings in one property and another array of complex objects in second property. In console view you can see

{FirstObject, SecondObject}

but after export will you get only full name of the object type in the defined property (CSV column). For example:

Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

The problem is that CSV is a flat file. If you want to keep information of the defined property then you will have to use different format of output file. For example modern JSON (JavaScript Object Notation: ConvertTo-Json, ConvertFrom-Json) that is not flattened object like CSV. Or you can use XML with Clixml cmdlets (ConvertTo-Xml, Export-Clixml, Import-Clixml) that are not suitable for all operations.

But if you insist to stay with CSV then you can serialize, project (Select-Object) and modify the object into flatten object that you can save as CSV. One of the possibilities is:

Get-ADUser -Filter * -Properties MemberOf |
    Select-Object `
    -ExcludeProperty MemberOf `
    -Property *,
        @{ Expression = { $_.MemberOf -join '; ' }; Label = 'MemberOf' } |
    Export-Csv `
    -Path 'C:\Temp\My Users from Active Directory.csv' `
    -Delimiter "`t" `
    -Encoding UTF8

Another possibility is to use function that I wrote.

Possibilities

It is possible to use ComputerName (invoke all operations) or Session (use established session) to export CSV on a remote server.
It is possible to join all arrays and collections or just values in defined property (in defined CSV column).
It is possible to serialize complex objects – it is possible to select and expand defined property of child object (using Select-Object -ExpandProperty) and join the output.

Examples

Serialize defined properties (join array into string); use another delimiter for these operations

Get-ADUser -Filter * -Properties MemberOf | Export-RvCsv `
    -Path 'D:\Temporary\ad-users.csv' `
    -PropertyJoin 'MemberOf'

Serialize all arrays, lists, collections in all properties

Get-ADUser -Filter * -Properties MemberOf | Export-RvCsv `
    -Path 'D:\Temporary\ad-users.csv' `
    -PropertyJoinArray

Serialize objects inside objects – defined properties of child objects will be selected, expanded and then joined

$items = Get-ADUser -Filter * -Properties *

$propertySelectAndJoin =
@(
    @{
        ParentPropertyName = 'dSCorePropagationData'
        ChildPropertyName  = 'Hour'
    },
    @{
        ParentPropertyName = 'Something2'
        ChildPropertyName  = 'PropertyInSomething2'
    }
)

$items | Export-RvCsv `
    -Path 'D:\Temporary\ad-users.csv' `
    -PropertySelectAndJoin $propertySelectAndJoin

Export CSV on remote server

$items | Export-RvCsv `
    -Path 'D:\Temporary\file_on_remove_device.csv'
    -ComputerName cont2test0.ad1.contoso.com, cont2test0.ad1.contoso.com

Export CSV on remote server and use existing session

$sessionItem = New-PSSession -ComputerName cont2test0.ad1.contoso.com

$items | Export-RvCsv `
    -Path 'D:\Temporary\file_on_remove_device.csv' `
    -Session $sessionItem

Disconnect-PSSession -Session $sessionItem | Remove-PSSession

Code

Function Export-RvCsv
{
    .SYNOPSIS
        Similar to Export-RvCsv with different default settings and with additional functions.

    .DESCRIPTION
        Developer
            Developer: Rudolf Vesely, http://rudolfvesely.com/
            Copyright (c) Rudolf Vesely. All rights reserved
            License: Free for private use only

            "RV" are initials of the developer's name Rudolf Vesely and distingue names of Rudolf Vesely's cmdlets from the other cmdlets.

        Description
            Similar to Export-RvCsv with different default settings and with additional functions.

        Requirements
            Developed and tested using PowerShell 4.0.

    .PARAMETER PropertyJoin
        It is possible to use all -Property* parameters but only a single action will be perfomed on each property in the following order: -PropertyJoin, -PropertySelectAndJoin, -PropertyJoinArray, -PropertyJoinAll

        List of all properties (columns in CSV) that should be joined.

    .PARAMETER PropertySelectAndJoin
        Designated for complex objects inside property (column of CSV) of the object that should be exported.

        Create hash table and define name of property of child object that will be selected and expanded (Select-Object -ExpandProperty) and name of the property (columns in CSV) where those child objects reside.

        Example of input:
            @(
                @{
                    ParentPropertyName = 'Something'
                    ChildPropertyName  = 'PropertyInSomething'
                },
                @{
                    ParentPropertyName = 'Something2'
                    ChildPropertyName  = 'PropertyInSomething2'
                }
            )

    .PARAMETER PropertyJoinArray
        Join all properties (values in all CSV columns) that have array or list data type.

    .PARAMETER PropertyJoinAll
        Try to join all properties.

    .PARAMETER JoinDelimiter
        Delelimeter for join operations.

    .EXAMPLE
        'Local: Serialize defined properties'
        Get-ADUser -Filter * -Properties MemberOf | Export-RvCsv `
            -Path ('C:\Temp\Export-RvCsv - {0}.csv' -f (Get-Date -Format yyyyMMdd)) `
            -PropertyJoin 'MemberOf'

    .EXAMPLE
        'Local: Serialize all arrays or collections in every property'
        Get-ADUser -Filter * -Properties * | Export-RvCsv `
            -Path ('C:\Temp\Export-RvCsv - {0}.csv' -f (Get-Date -Format yyyyMMdd)) `
            -PropertyJoinArray

    .EXAMPLE
        'Local: Defined properties of child objects will be selected and expanded; Join rest of the properties'
        $items = Get-ADUser -Filter * -Properties *

        $propertySelectAndJoin =
        @(
            @{
                ParentPropertyName = 'dSCorePropagationData'
                ChildPropertyName  = 'Hour'
            },
            @{
                ParentPropertyName = 'Something2'
                ChildPropertyName  = 'PropertyInSomething2'
            }
        )

        $items | Export-RvCsv `
            -Path ('C:\Temp\Export-RvCsv - {0}.csv' -f (Get-Date -Format yyyyMMdd)) `
            -PropertySelectAndJoin $propertySelectAndJoin `
            -PropertyJoinAll

    .EXAMPLE
        'ComputerName: Default configurations of export'
        $items | Export-RvCsv `
            -Path 'C:\Temp\file_on_remove_device.csv'
            -ComputerName cont2test0.ad1.contoso.com, cont2test0.ad1.contoso.com

    .EXAMPLE
        'Session: Default configurations of export'
        $sessionItem = New-PSSession -ComputerName cont2test0.ad1.contoso.com

        $items | Export-RvCsv `
            -Path 'C:\Temp\file_on_remove_device.csv' `
            -Session $sessionItem

        Disconnect-PSSession -Session $sessionItem | Remove-PSSession

    .INPUTS

    .OUTPUTS

    .LINK
        https://techstronghold.com/
    #>

    [CmdletBinding(
        DefaultParametersetName = 'Path',
        SupportsShouldProcess = $true,
        PositionalBinding = $false,
        HelpURI = 'https://techstronghold.com/',
        ConfirmImpact = 'Medium'
    )]

    Param
    (
        [Parameter(
            Mandatory = $true,
            Position = 0,
            ParameterSetName = 'Path'
        )]
        [ValidateLength(1, 255)]
        [Alias('FullName')]
        [string]$Path,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowEmptyString()]
        [ValidateLength(0, 10)]
        [string]$Delimiter = "`t",

        [Parameter(
            Mandatory = $true,
            # Position = ,
            # ParameterSetName = ''
            ValueFromPipeline = $true
        )]
        $InputObject,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateLength(1, 127)]
        [string[]]$PropertyJoin,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [System.Collections.Hashtable[]]$PropertySelectAndJoin,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$PropertyJoinArray = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$PropertyJoinAll = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowEmptyString()]
        [ValidateLength(0, 10)]
        [string]$JoinDelimiter = "; ",

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateSet(
            'ASCII',
            'UTF8'
        )]
        [string]$Encoding = 'UTF8',

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$NoTypeInformation = $true,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$Append = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$NoClobber = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$Force = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowNull()]
        [ValidateLength(1, 255)]
        [string[]]$ComputerName,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowNull()]
        [System.Management.Automation.Runspaces.PSSession[]]$Session
    )

    Begin
    {
        # Configurations
        $ErrorActionPreference = 'Stop'
        if ($PSBoundParameters['Debug']) { $DebugPreference = 'Continue' }
        Set-PSDebug -Strict
        Set-StrictMode -Version Latest

        #region Functions
        Function Export-RvCsvProcess
        {
            [CmdletBinding(
                DefaultParametersetName = 'Path',
                SupportsShouldProcess = $true,
                PositionalBinding = $true,
                HelpURI = 'https://techstronghold.com/',
                ConfirmImpact = 'Medium'
            )]

            Param
            (
                [Parameter(
                    Mandatory = $true,
                    Position = 0,
                    ParameterSetName = 'Path'
                )]
                [string]$Path,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowEmptyString()]
                [string]$Delimiter,

                [Parameter(
                    Mandatory = $true,
                    # Position = ,
                    # ParameterSetName = ''
                    ValueFromPipeline = $true
                )]
                $InputObject,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [string[]]$PropertyJoin,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [System.Collections.Hashtable[]]$PropertySelectAndJoin,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$PropertyJoinArray,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$PropertyJoinAll,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowEmptyString()]
                [string]$JoinDelimiter,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [string]$Encoding,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$NoTypeInformation,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$Append,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$NoClobber,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$Force
            )

            Begin
            {
                # Configurations
                $ErrorActionPreference = 'Stop'
                if ($PSBoundParameters['Debug']) { $DebugPreference = 'Continue' }
                Set-PSDebug -Strict
                Set-StrictMode -Version Latest
            }

            Process
            {
                if ($PropertyJoin -or $PropertySelectAndJoin -or $PropertyJoinArray -or $PropertyJoinAll)
                {
                    $outputItems = @()

                    foreach ($item in $InputObject)
                    {
                        $itemSerialized = New-Object -TypeName PSObject

                        foreach ($property in $item.PSObject.Properties)
                        {
                            $name     = $property.Name
                            # $type = $(if ($property.Value) { $property.Value.GetType().FullName } else { '' })
                            $type = $property.TypeNameOfValue
                            $value    = $property.Value

                            Write-Verbose -Message ('    - {0} ({1}): {2}' -f $name, $type, $value)

                            if ($value -ne $null)
                            {
                                $selectAndJoinItem = $(if ($PropertySelectAndJoin) { $PropertySelectAndJoin | Where-Object -Property ParentPropertyName -EQ -Value $name })

                                if ($PropertyJoin -contains $name)
                                {
                                    $value = $value -join $JoinDelimiter
                                }

                                elseif ($selectAndJoinItem)
                                {
                                    $value = ($value | Select-Object -ExpandProperty $selectAndJoinItem.ChildPropertyName) -join $JoinDelimiter
                                }

                                elseif ($PropertyJoinAll -or
                                    ($PropertyJoinArray -and
                                    ($type -match '.*\[\]$' -or
                                    $type -like '*ADPropertyValueCollection')))
                                {
                                    $value = $value -join $JoinDelimiter
                                }
                            }

                            $itemSerialized |
                                Add-Member -MemberType NoteProperty `
                                -Name $name `
                                -Value $value
                        }

                        $outputItems += $itemSerialized
                    }
                }
                else
                {
                    $outputItems = $InputObject
                }

                $outputItems | Export-Csv `
                    -Path $Path `
                    -Delimiter $Delimiter `
                    -Encoding $Encoding `
                    -NoTypeInformation:$NoTypeInformation `
                    -Append:$Append `
                    -NoClobber:$NoClobber `
                    -Force:$Force
            }

            End
            {
            }
        }
        #endregion

        # Variables
        $allData = @()
    }

    Process
    {
        $allData += $InputObject
    }

    End
    {
        # Remote device: Session
        if ($Session)
        {
            foreach ($sessionItem in $Session)
            {
                Invoke-Command `
                    -Session $sessionItem `
                    -ArgumentList $Path, $Delimiter, $allData, $PropertyJoin, $PropertySelectAndJoin, $PropertyJoinArray, $PropertyJoinAll, $JoinDelimiter, $Encoding, $NoTypeInformation, $Append, $NoClobber, $Force `
                    -ScriptBlock ${Function:Export-RvCsvProcess}
            }
        }
        else
        {
            foreach ($computerNameItem in $(if ($ComputerName) { $ComputerName } else { '.' } ))
            {
                # Local device
                if (!$computerNameItem -or $computerNameItem -eq '.' -or $computerNameItem -eq $env:COMPUTERNAME)
                {
                    Export-RvCsvProcess `
                        -Path                   $Path `
                        -Delimiter              $Delimiter `
                        -InputObject            $allData `
                        -PropertyJoin           $PropertyJoin `
                        -PropertySelectAndJoin  $PropertySelectAndJoin `
                        -PropertyJoinArray      $PropertyJoinArray `
                        -PropertyJoinAll        $PropertyJoinAll `
                        -JoinDelimiter          $JoinDelimiter `
                        -Encoding               $Encoding `
                        -NoTypeInformation      $NoTypeInformation `
                        -Append                 $Append `
                        -NoClobber              $NoClobber `
                        -Force                  $Force
                }

                # Remote device: ComputerName
                else # if ($ComputerName)
                {
                    Invoke-Command `
                        -ComputerName $computerNameItem `
                        -ArgumentList $Path, $Delimiter, $allData, $PropertyJoin, $PropertySelectAndJoin, $PropertyJoinArray, $PropertyJoinAll, $JoinDelimiter, $Encoding, $NoTypeInformation, $Append, $NoClobber, $Force `
                        -ScriptBlock ${Function:Export-RvCsvProcess}
                }
            }
        }
    }
}

Technology Stronghold