See Why PowerShell Can't Export Some Properties to CSV

See Why PowerShell Can't Export Some Properties to CSV

  • Comments 3
  • Likes

Summary: Learn why Windows PowerShell cannot export certain properties to a CSV file and what to do about it.

 

Microsoft Scripting Guy, Ed Wilson, is here. The other day, Terry Dow posted a comment on my Use PowerShell to Work with CSV Formatted Text blog post. Basically, Terry is asking about exporting a multivalue property via Export-CSV file. I can illustrate the problem by using the Get-Process cmdlet to retrieve information about my currently running Outlook process. In this command, I pipe the results of the Get-Process cmdlet to the Format-List cmdlet. Here is the command:

Get-Process outlook | format-list *

The output is really extensive, and I am going to point out that one line appears like the one seen here:

Threads                    : {7148, 8120, 5320, 3080...}

I am now going to select the name and the threads properties, and pipe them to the Format-List cmdlet:

Get-Process outlook | format-list name, threads

The output does not contain all of the thread information. In fact, it only has a few numbers, followed by an ellipsis. I try several combinations, but nothing other than a few numbers and ellipses ever appear. This is shown in the following figure.

Image of unsatisfactory output

This behavior carries over when piping information to Export-CSV. In the following command I use the Get-Process cmdlet to retrieve the Outlook process. I pipe the resultant Process object to the Select-Object cmdlet (select is an alias) and retrieve the name and the threads, and pipe to Export-CSV to create a CSV file. I then open the file in Microsoft Excel.

Get-Process outlook | Select name, threads | export-CSV c:\fso\olproc.csv; c:\fso\olproc.csv

The resultant Microsoft Excel spreadsheet appears in the following figure (I highlighted the threads collection manually).

Image of resultant Excel spreadsheet

So, we have confirmed that for certain types of properties, Windows PowerShell does not display all the information. We seem to be a bit better off when using Format-Table or Format-List (but that is just an illusion).

The first thing we need to do is to see which type of object we are dealing with. To do this, I use the Get-Member cmdlet. This command is shown here:

PS C:\> Get-Process outlook | Get-Member threads

 

   TypeName: System.Diagnostics.Process

 

Name                           MemberType                                                     Definition

Threads Property           System.Diagnostics.ProcessThreadCollection         Threads {get;}

 

The threads property returns a ProcessThreadCollection. This class is documented on MSDN, but I do not need to really look at that article because I can find out what I need via the Get-Member cmdlet. The big thing to keep in mind is that a ProcessThreadCollection class is a collection of something else. This something else is a ProcessThread. The ProcessThread class is also documented on MSDN, and it is more interesting than the ProcessThreadCollection. I can find the information about the ProcessThread class by using the following command (gm is an alias for the Get-Member cmdlet):

(Get-Process outlook).threads | gm

The command and associated output are shown here:

PS C:\> (Get-Process outlook).threads | gm

 

   TypeName: System.Diagnostics.ProcessThread

 

Name                             MemberType                             Definition

Disposed                       Event                                        System.EventHandler Disposed(System.Object, System.EventArgs)

CreateObjRef                 Method                                     System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)

Dispose                         Method                                     System.Void Dispose()

Equals                           Method                                     bool Equals(System.Object obj)

GetHashCode                Method                                     int GetHashCode()

GetLifetimeService          Method                                     System.Object GetLifetimeService()

GetType                          Method                                     type GetType()

InitializeLifetimeService   Method                                  System.Object InitializeLifetimeService()

ResetIdealProcessor      Method                                  System.Void ResetIdealProcessor()

ToString                            Method                                     string ToString()

BasePriority                     Property                                    System.Int32 BasePriority {get;}

Container                        Property                                    System.ComponentModel.IContainer Container {get;}

CurrentPriority               Property                                    System.Int32 CurrentPriority {get;}

Id                                      Property                                    System.Int32 Id {get;}

IdealProcessor               Property                                    System.Int32 IdealProcessor {set;}

PriorityBoostEnabled      Property                                    System.Boolean PriorityBoostEnabled {get;set;}

PriorityLevel                   Property                                    System.Diagnostics.ThreadPriorityLevel PriorityLevel {get;set;}

PrivilegedProcessorTime Property                                    System.TimeSpan PrivilegedProcessorTime {get;}

ProcessorAffinity            Property                                    System.IntPtr ProcessorAffinity {set;}

Site                               Property                                    System.ComponentModel.ISite Site {get;set;}

StartAddress                  Property                                    System.IntPtr StartAddress {get;}

StartTime                      Property                                    System.DateTime StartTime {get;}

ThreadState                   Property                                    System.Diagnostics.ThreadState ThreadState {get;}

TotalProcessorTime        Property                                    System.TimeSpan TotalProcessorTime {get;}

UserProcessorTime         Property                                    System.TimeSpan UserProcessorTime {get;}

WaitReason                   Property                                    System.Diagnostics.ThreadWaitReason WaitReason {get;}

 

I can write the threads to a CSV file. To do this, I can use the Select-Object cmdlet and pipe the results to the Export-CSV file. This command is shown here (I use Select as an alias for the Select-Object cmdlet, and I open the CSV in Microsoft Excel by using the path to the newly created CSV file):

Get-Process outlook | Select -ExpandProperty threads | Export-CSV c:\fso\OlThread.csv;c:\fso\olthread.csv

The newly created CSV file opens easily in Microsoft Excel. This allows me to examine the various threads in Microsoft Outlook (they are all waiting by the way).

Image of newly created CSV file opened in Excel

So the solution to having an object name showing up in the CSV file is to use the Select-Object cmdlet to expand the object contained inside the property. Keep in mind that this will not enable you to be able to have the process name, processID, and threads in the same CSV file because of the way Select-Object works when expanding processes.

Use Export-CliXML to maintain fidelity to the original object. The following code illustrates this technique:

Get-Process outlook | Export-Clixml c:\fso\olXML.xml

To work with the object, use the Import-Clixml cmdlet as illustrated here where I import the XML file, and access the name and the process ID.

$a = Import-Clixml C:\fso\olXML.xml

$a.Name

$a.Id

I get all of the thread information by accessing the threads property. In the following example, I truncate the output:

PS C:\> $a.threads 

 

BasePriority            : 8

CurrentPriority         : 13

Id                      : 7148

PriorityBoostEnabled    : True

PriorityLevel           : Normal

<***OUTPUT TRUNCATED***>

 

Remember back at the beginning of the article when I was talking about the ProcessThreadCollection class? I said the ProcessThreadCollection class was not so interesting. Well, there is one interesting property, and it is the count property. I can use dotted notation to access it, as illustrated here:

PS C:\> $a.threads.count

39

Knowing the number of threads a process uses is valuable information. In fact, using the technique above, I can add it to an object by using the following technique (gps is an alias for the Get-Process cmdlet and select is an alias for Select-Object):

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}}

This code works, but the number of threads is spread out everywhere (not sorted). To sort the number of threads, I pipe the command to the Sort-Object cmdlet. My label, number of threads, is the actual property name in my newly created custom object. This command is shown here:

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}} | sort 'number of threads'

This works great, but now the information spreads to both sides of the Windows PowerShell console window. Now, I will use the Format-Table cmdlet (ft is an alias and a is short for autosize).

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}} | sort 'number of threads' | ft –a

The command and associated output are shown in the following figure.

Image of command and associated output

Of course, it would be possible to pipe this type of expression to Export-CSV. I hope you enjoyed today’s excursions into more fun with CSV files.

Join me tomorrow for more cool Windows PowerShell stuff.

 

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Ed,

    nothing to add except ...:

    Complicated things (like threads) map to complicted data structures in powershell.

    That's the way life is :-)

    Klaus

  • @Klaus Schulte yep :-) some things are just a bit complicated. Luckily with PowerShell it is possible to look under the covers to discover what is going on.

  • Hi Ed,

    After reading this post, I'm thinking that what I try to accomplish just isn't possible...

    but I'm kinde hoping you might still have an idea I have not thought about!

    What I try to accomplish:

    -Script that lists all ADgroups as headers and all ADgroupmembers underneath the groupnames in a csv.

    -example:

    "ADMINISTRATORS","USERS","EDITORS"

    "John Doe","Jasper Doe","Jack Doe"

    "Amidalia Doe","Peter Doe","Nick Doe"

    What I tried:

       $o = new-object psobject

       get-adgroup -filter * |% {$o | add-member NoteProperty -name ($_).name -value (get-adgroupmember $_).name}

       $o | export-csv "GroupMembers.csv"

    The problem I get:

    -What this blogpost is about: "System.Object[]"

    I hope you have something up your sleeve I haven't thought about!!

    Geert