Repairing CSV Exports (Part 2)

by ps1May 25, 2021

In the previous tip we pinpointed a general problem when converting objects to CSV: any property containing an array will display the array data type instead of the array content. Here is an example:

 
PS> Get-Service | Select-Object -Property Name, DependentServices, RequiredServices | ConvertTo-Csv

#TYPE Selected.System.ServiceProcess.ServiceController
"Name","DependentServices","RequiredServices"
"AarSvc_e1277","System.ServiceProcess.ServiceController[]","System.ServiceProcess.ServiceController[]"
"AdobeARMservice","System.ServiceProcess.ServiceController[]","System.ServiceProcess.ServiceController[]"

In the previous tip we also showed a manual solution to the problem: you can always manually convert the content of any array property to a string, using the -join operator:

Get-Service | 
  Select-Object -Property Name, DependentServices, RequiredServices | 
  
  ForEach-Object {
    $_.DependentServices = $_.DependentServices -join ','
    $_.RequiredServices = $_.RequiredServices -join ','
    return $_
  } |
  
  ConvertTo-Csv

The data is now “repaired”, and the content of array properties displays correctly:

 
"Name","DependentServices","RequiredServices" 
"AppIDSvc","applockerfltr","RpcSs,CryptSvc,AppID"
"Appinfo","","RpcSs,ProfSvc"
"AppVClient","","AppvVfs,RpcSS,AppvStrm,netprofm"
"AppXSvc","","rpcss,staterepository"
"AssignedAccessManagerSvc","",""
...

However, turning array properties into flat strings can be a lot of manual work, so here is a new function called Convert-ArrayPropertyToString which does all the conversion automatically:

function Convert-ArrayPropertyToString
{
  process
  {
    $original = $_
    Foreach ($prop in $_.PSObject.Properties)
    {
      if ($Prop.Value -is [Array] -and $prop.MemberType -ne 'AliasProperty')
      {
        Add-Member -InputObject $original -MemberType NoteProperty -Name $prop.Name -Value ($prop.Value -join ',') -Force  
      }
    }
    $original
  }
}

To convert objects to CSV without losing array information, simply pipe it through the new function:

 
PS> Get-Service | Select-Object -Property Name, DependentServices, RequiredServices | Convert-ArrayPropertyToString | ConvertTo-Csv

#TYPE Selected.System.ServiceProcess.ServiceController
"Name","DependentServices","RequiredServices"
"AarSvc_e1277","",""
"AppIDSvc","applockerfltr","RpcSs,CryptSvc,AppID"
"Appinfo","","RpcSs,ProfSvc"
"AppMgmt","",""
"AppReadiness","",""
"AppVClient","","AppvVfs,RpcSS,AppvStrm,netprofm"

Amazing, eh? The new function does all the work for you, and it works with any objects:

 
PS> [PSCustomObject]@{
  Name = 'Tobias'
  Array = 1,2,3,4
  Date = Get-Date
} | Convert-ArrayPropertyToString

Name   Date                Array  
----   ----                -----  
Tobias 06.05.2021 11:30:58 1,2,3,4

Convert-ArrayPropertyToString uses the secret PSObject property found on any PowerShell object to get the property names. Next, it checks all properties for array content. If found, it automatically converts the array to a comma-separated string.

To be able to overwrite existing properties with the new flat string content – even when properties are write-protected – it uses Add-Member and shadows the properties using -Force. The new flat string content is not really overwriting properties. Instead, they are added and take precedence. In effect, any object – even when its properties are write-protected – can be adjusted.

Now whenever you need to create Excel reports or export data to CSV, you can preserve array content easily.

ReTweet this Tip!

Repairing CSV Exports (Part 2)

Tags