An Engineers Notebook

Export-CSV not working | System.String[] | Blank Column

When trying to Export to CSV in PowerShell, you may get a blank column, or System.String[] in your CSV.

Why this happens

This is happening because your trying to export property that has multiple values.

Example

If we try to export a message tracking log in exchange like this, it will give a blank column for recipients:

get-messagetrackinglog -recipient "[email protected]" -server "hvex2013" -start "09/01/2016 09:00:00 AM" -end "03/24/2017 13:01:00 PM" -resultsize unlimited |select Sender,Recipient,recipientstatus,Messagesubject,Timestamp,Eventid,Source,Sourcecontext,messageid,internalmessageid,clientip,clienthostname,serverip,serverhostname,connectorid,totalbytes,recipientcount,relatedrecipientaddress,reference,returnpath,messageinfo | export-csv C:messagetracking.csv


Solution

For any column like this you need to use the “Join” function and run the query like this instead:

$msgs = Get-MessageTrackingLog -sender "[email protected]" -resultsize Unlimited
$msgs | Where-Object {$_.timestamp -gt "MM/DD/YYYY 09:00:00" -and $_.timestamp -lt "MM/DD/YYYY 23:59:59"} | Select Sender,timestamp,@{name='Recipients';Expression={[string]::join(";",($_.Recipients))}},MessageSubject,TotalBytes | Export-CSV -Path C:Messages.csv

 Notice the recipient part in the list of selected columns is different.

Useful links where I learned this:

http://blog.millersystems.com/powershell-exporting-multi-valued-attributes-via-export-csv-cmdlet/

 

Leave a Reply

Your email address will not be published.