Repairing CSV Exports (Part 1)

by May 21, 2021

When you convert data to CSV you may discover a pretty disturbing phenomenon: some properties no longer display the original data. 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[]"  
 

As you see, both the DependentServices and RequiredServices properties always show the same content for all services.

This occurs when properties contain arrays. Flat two-dimensional export formats such as CSV cannot display arrays, so instead the array data type is displayed. That of course is not helpful at all.

Before we move to a solution: what you see here is a severe problem in many scenarios. It not only affects CSV exports but also exports to Excel or other two-dimensional table formats.

To solve the problem, you’d have to convert any array into a string. You can do this manually or automatically. In this trick we show the manual approach first to focus on the effect. In an upcoming tip we do the same automatically.

Here is the manual approach to correctly export the selected data from above:

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

The data now displays all array content because the ForEach-Object loop has converted the array content to a comma-separated string, using the -join operator.

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

This “adjustment” is possible whenever you ran the original data through Select-Object: Select-Object always copies (clones) the information so once data has been processed by Select-Object, you own the objects and can change its properties in any way you want.


Twitter This Tip! ReTweet this Tip!