Why Can’t I Pipe Format-Table to Export-Csv (and get something useful)?

Ken’s PowerShell blog has an entry Remote Services and PowerShell where he wrote a function to get services from a remote machine using WMI. He formatted the data using FT (format-table) and then went on to export the data to CSV. He pointed out that the following would NOT work

rget-service remotemachinename netlogon | ft -autosize name,startmode,state,status,startname | export-csv services.csv

Instead you should use:

rget-service remotemachinename netlogon | Select-Object name,startmode,state,status,startname | export-csv services.csv

Let’s take a minute to explore why the first command does not work. Let’s replace the Export-CSV with a Get-Member and see what we get:

Ps> rget-service remotemachinename netlogon | ft -autosize name,startmode,state,status,startname | Get-Member –MemberType Property

TypeName:
Microsoft.PowerShell.Commands.Internal.Format.FormatStartData

Name MemberType Definition
—- ———- ———-
autosizeInfo Property Microsoft.PowerShell….
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId…
groupingEntry Property Microsoft.PowerShell….
pageFooterEntry Property Microsoft.PowerShell….
pageHeaderEntry Property Microsoft.PowerShell….
shapeInfo Property Microsoft.PowerShell….


TypeName:
Microsoft.PowerShell.Commands.Internal.Format.GroupStartData

Name MemberType Definition
—- ———- ———-
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId…
groupingEntry Property Microsoft.PowerShell….Pitn
shapeInfo Property Microsoft.PowerShell….


TypeName:
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData

Name MemberType Definition
—- ———- ———-
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId…
formatEntryInfo Property Microsoft.PowerShell….
outOfBand Property System.Boolean outOfB…
writeErrorStream Property System.Boolean writeE…


TypeName:
Microsoft.PowerShell.Commands.Internal.Format.GroupEndData

Name MemberType Definition
—- ———- ———-
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId…
groupingEntry Property Microsoft.PowerShell….


TypeName:
Microsoft.PowerShell.Commands.Internal.Format.FormatEndData

Name MemberType Definition
—- ———- ———-
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId…
groupingEntry Property Microsoft.PowerShell….


What you are seeing is that Format-Table transforms the objects into a stream of formatting directives. These are then consumed by one of the OUT- Commands (Out-Host, Out-File, Out-String, Out-Printer). This is why you can’t pipe format-table to export-csv.

Enjoy!

Jeffrey Snover [MSFT]
Windows PowerShell/MMC Architect
Visit the Windows PowerShell Team blog at: http://blogs.msdn.com/PowerShell
Visit the Windows PowerShell ScriptCenter at: http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx