Powershell – Piping Objects From Format-Table to Export-CSV Has Unexpected Results

activesyncexchange-2010powershell

I'm running this command to get a list of all Activesync users and export them to C:\activesync.csv

Get-ActiveSyncDevice | Get-ActiveSyncDeviceStatistics | sort-object status, devicetype , lastsyncattempttime | ft  FirstSyncTime   ,LastPolicyUpdateTime   ,LastSyncAttemptTime    ,LastSuccessSync , DeviceType , DeviceID, DeviceAccessState, Identity -a | Export-Csv c:\activesync.csv

The problem is that the CSV data doesn't match the console display if I omit the trailing | c:\activesync.csv … where the data and columns displayed don't match.

Is this a bug in powershell?

Best Answer

The thing to remember is that everything in PowerShell is an object. When you use Format-Table, the output is a formatting object and that's what is getting passed in to export CSV. If you pipe the results of the Format-Table command to Get-Member, you'll see what I mean.

Get-ActiveSyncDevice | 
  Get-ActiveSyncDeviceStatistics | 
  sort-object status, devicetype , lastsyncattempttime | 
  Format-Table FirstSyncTime   ,LastPolicyUpdateTime   ,LastSyncAttemptTime    ,LastSuccessSync , DeviceType , DeviceID, DeviceAccessState, Identity -a | 
  Get-Member

The output of that will be a series of descriptions of various formatting objects.

Format-Table is great for creating output in your console session or sending formatted output to a text file (using out-file), but if we change your Format-Table to Select-Object, your CSV file will be more of what you expect.

Get-ActiveSyncDevice | 
  Get-ActiveSyncDeviceStatistics | 
  sort-object status, devicetype , lastsyncattempttime | 
  select  FirstSyncTime   ,LastPolicyUpdateTime   ,LastSyncAttemptTime    ,LastSuccessSync , DeviceType , DeviceID, DeviceAccessState, Identity | 
  Export-Csv c:\activesync.csv

(Side note.. for long pipelines the pipe character can be used as a line continuation.)

Related Topic