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

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

  • Comments 16

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

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Very Helpful! Thanks for your insight Jeffrey.

  • I actually found a similar intriguing behavior earlier as well but Ken beat me to the punch before I get around to blog about it (still :(.

    Anyway, the statements I was looking at were (still am if the odd behavior has not already been explained here by Jeffrey):

    PS C:\> get-service | sort-object status, name | ft -groupby status | convertto-html | out-file "get-service(bad).html"

    PS C:\> get-service | sort-object status, name | convertto-html | out-file "get-service(good).html"

    PS C:\temp>

    rgds,

  • When you format the data using Format-table and then try to export it using Export-Csv it doesn't

  • Great info and thanks for the explanation.  This will help us speed up an e2k3 -> e2k7 transition.

  • Google led me here for my problem, i can't find anything similair somewhere else. I'm also trying to get the output into a csv file but i don't know where to put my export-csv command.

    Here's the script(let)

    $mbx = get-casmailbox | where {$_.hasactivesyncdevicepartnership -eq $true -and $_.identity -notlike "*CAS_{*"} ; $mbx | foreach {$name = $_.name; $device = get-activesyncdevicestatistics -mailbox $_.identity; $device | foreach {Write-host $mbx.name, $_.identity, $_.devicemodel, $_.devicephonenumber, $_.deviceid, $_.FirstSyncTime, $_.LastSuccessSync } }

    I've put it at the end between the curly's everywhere. When export did not work i try'd Out-File Write-File....

    Nothing! i don't get it ? How to get my output to a file ?

    Regards, Diederik

  • OK, we ran into this very question this morning in class.  The "why" was pretty obvious.  But what's the solution?

  • The solution I normally use is Select-Object.  Below a small number of properties (4), Select-Object will display as a table.  Sadly, it cannot autosize, but what you send along the pipeline will contain the properties of the original object.

    Hope this Helps,

    James Brundage [MSFT]

  • DiederikM,

    Not sure if it will help or not but I ran into a similar problem with AD queries I was pulling with powershell. I ended up having to pull the data as [string]$_.name into my custom object to be able to use | export-csv.

  • Hi Jeff,

    This post helped me to complete one of my project based on exchange 2010 and powershell gui.

    I was stuck on the issue for 2 days and got this post very useful.

    Thank you very much :)

  • Thanks for the post.  I was stuck for a while and this did the trick

  • I'm gettting this error but I'm not sure how to out put the results to email message. I'm trying to output the results of $svc to $body

    Set-ExecutionPolicy RemoteSigned

    $svc=gwmi win32_service | where {$_.StartMode -eq “Auto”} |where {$_.State -eq "Stopped"}| select-object  DisplayName,name,startname,startmode,state | format-table

    $svc

    gwmi win32_service | where {$_.StartMode -eq “Auto”} |where {$_.State -eq "Stopped"}| Start-Service | Start-Sleep  -s 10 | select DisplayName,name,startname,startmode,state |format-table

    $emailFrom = "insert email address here"

    $emailTo = "insert email address here"

    $subject = "Services"

    $body = "insert message body here"

    $smtpServer = "relay.golder.com"

    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

    $smtp.Send($emailFrom, $emailTo, $subject, $body)

  • This was exactly what I was looking for. Thanks :)

  • How about further explaining how we can still use the format-table without the stream of formatting directives ?

    I often find myself needing to format objects like the format-table at the same time having the benefits of select-object, but, somehow select-obect only turns automatically into a table objects with no more than 4 properties ( at least this is my experience ).

    Thanks

  • Thanks a lot! this helped me a lot.

  • Just replaced 'ft' with 'Select' and voila, all the results were displayed in the format that I wanted.

    Thanks a lot. :)

Page 1 of 2 (16 items) 12