Converting Arrays to Strings in CSV Exports

by Mar 1, 2016

When you export objects to CSV–for example to display them in Excel–arrays won't be output correctly. Here is a simple way that converts arrays to strings prior to outputting them to CSV:

#requires -Version 2 $Path = "$env:tempreport.csv" $hash = @{ Name = 'ReplacementStrings' Expression = { $_.ReplacementStrings -join ',' } } Get-EventLog -LogName system -EntryType error -Newest 5 | Select-Object -Property Message, MachineName, Source, $hash | Export-CSV -UseCulture -NoTypeInformation -Encoding UTF8 -Path $Path Invoke-Item -Path $Path 

When you run this code, PowerShell finds the latest 5 errors in the system eventlog. The ReplacementStrings property is an array of keywords and cannot be exported to CSV. This is why the script uses a hash table that defines a new property named ReplacementStrings that is to replace the existing ReplacementStrings property. The content of the new property combines the array to a string, using the –join operator.

Now the export to CSV works well, and when you open the file in Excel, the replacementstrings keywords show up.

Twitter This Tip! ReTweet this Tip!