When you send information to Microsoft Excel, it is converted to text using the built-in .NET ToString() method. This method typically does not convert well any arrays or non-primitive data types.
Here is an example illustrating the problem. It creates a report of the 10 most recent error events in your System event log:
$Path = "$env:temp\$(Get-Random).csv" Get-EventLog -LogName System -EntryType Error -Newest 10 | Select-Object EventID, MachineName, Data, Message, Source, ReplacementStrings, InstanceId, TimeGenerated | Export-Csv -Path $Path -Encoding UTF8 -NoTypeInformation -UseCulture Invoke-Item -Path $Path
The columns "Data" and "ReplacementStrings" is unusable. Since both properties contain arrays, the auto-conversion simply displays the name of the data type. This is a phenomenon found often with Excel reports created from object data.
To improve the report, you can explicitly use the PowerShell engine to convert object to text, then turn multiple lines of text in one single line of text.
You can do this for any column that does not seem to contain the correct result. Here is a solution for the previous example that improves the columns Message, Data, and ReplacementStrings:
$Path = "$env:temp\$(Get-Random).csv" Get-EventLog -LogName System -EntryType Error -Newest 10 | Select-Object EventID, MachineName, Data, Message, Source, ReplacementStrings, InstanceId, TimeGenerated | ForEach-Object { $_.Message = ($_.Message | Out-String -Stream) -join ' ' $_.Data = ($_.Data | Out-String -Stream) -join ', ' $_.ReplacementStrings = ($_.ReplacementStrings | Out-String -Stream) -join ', ' $_ } | Export-Csv -Path $Path -Encoding UTF8 -NoTypeInformation -UseCulture Invoke-Item -Path $Path
Now all columns show correct results. Note how the problematic properties were first sent to Out-String (using PowerShell's internal mechanism to convert the data to meaningful text), then -join was used to turn the information to a single line of text.
Note also how the property "Message" was processed. Although this property seemed to be OK, in reality it can be a multiline text. Multiline messages would only show the first line in Excel, appended by "…". By joining the lines with a space, Excel shows the full message.
