none
Format Table not displaying all columns from array

    Question

  • Hi,

    I'm wanting to use PowerShell to build a dynamic array which will contain an unknown number of colunms. What I intend on doing is looping through users within AD to find various attributes, one of these is ProxyAddresses. What I want to do is loop through, counting and writing, the number of SMTP addresses a user has and putting them into a seperate column on the users row. Unfortunaltey this number isn't static and I do not know how many to set as a maxiumn when defining the variable and so which is why I want to dynamically add the columns.

    So what I thought on doing was creating an object to store these settings, and incrementing as needed.

    That part is fine, I know how to loop through what I need to and increment the header as needed. But when I try to output the array to a csv, or format-table, it does not show all the values within the array, even though I can select the object value and output that value if specified.

    I suppose what I'm getting at is how do I output the entire array (all columns whether null or not) to a csv file?

    He is a snippet of code. This is just for example purposes to show you what I mean about the array not exporting correctly.


    $myArray = @()

    #===============================

    $myObject = $null
    $myObject = New-Object System.Object

    $myObject | Add-Member -type NoteProperty -name ComputerName -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader2 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader3 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader4 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader5 -Value "N/A"

    $myObject.ComputerName = "test1"

    $myObject | Add-Member -Type noteproperty -Name TestHeader1 -Value "testing123"

    $myArray += $myObject

    #===============================

    $myObject = $null
    $myObject = New-Object System.Object

    $myObject | Add-Member -type NoteProperty -name ComputerName -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader2 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader3 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader4 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader5 -Value "N/A"

    $myObject.ComputerName = "test2"

    $myArray += $myObject

    #===============================

    $myObject = $null
    $myObject = New-Object System.Object

    $myObject | Add-Member -type NoteProperty -name ComputerName -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader2 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader3 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader4 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader5 -Value "N/A"

    $myObject.ComputerName = "test3"

    $myObject | Add-Member -Type noteproperty -Name TestHeader1 -Value "testing345"
    $myObject | Add-Member -Type noteproperty -Name AnotherHeader -Value "testing567"

    $myArray += $myObject

    #===============================

    $myObject = $null
    $myObject = New-Object System.Object

    $myObject | Add-Member -type NoteProperty -name ComputerName -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader2 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader3 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader4 -Value "N/A"
    $myObject | Add-Member -type NoteProperty -name DefaultHeader5 -Value "N/A"

    $myObject.ComputerName = "test4"

    $myArray += $myObject

    #===============================

    #$myArray     # Shows everything
    $myArray | ft   # Doesn't show all columns
    #$myArray | Select-Object ComputerName, AnotherHeader | ft # Shows the hidden column??

     

    Tuesday, October 15, 2013 12:26 PM

Answers

  • The header for Format-Table and Export-CSV is based on the first item in the array. If you output the item which has most columns (this works since you only add columns) first it will work. Based on your example:

    $myArray[2,0,1,3,4] | ft $myArray | sort {($_ | Get-Member -MemberType NoteProperty).Count} -Descending | Export-Csv -NoTypeInformation -Path $env:TEMPC:\test.csv



    • Edited by Dirk_74 Tuesday, October 15, 2013 2:38 PM
    • Marked as answer by james_stap Wednesday, October 16, 2013 1:09 PM
    Tuesday, October 15, 2013 2:37 PM
  • Note that when building objects it is more reliable to use this method.  It is also easier.

    $properties=@{
         Colums1=$null
        Colums1=$null
        Colums1=$null
        Colums1=$null
        Colums1=$null
    }

    After that we assign values to the hash

    $properties.Column1='myvalue'

    After collecting all values that may exists we can generate the object.

    New-Object PsObject -Property $properties

    This will guarantee that all objects are identical.  We can also use variations depending on the kind of loop we are in.

    You can NEVER generate an object that has a variable number of properties into a CSV. The first object output will control the number and type of column in the CSV.

    An object is identified by the number and type of its properties. Objects with different properties are not of the same type.  (OOP 101)


    ¯\_(ツ)_/¯

    • Marked as answer by james_stap Wednesday, October 16, 2013 1:09 PM
    Tuesday, October 15, 2013 4:16 PM

All replies

  • Dynamic arrays cannot be exported to CSV.  CSV requires ficed columns.

    To add a variable number of addresses to a value do the following:

    $smtpAddresses=@()
    $smtpAddresses+='name@dom.com'
    $smtpAddresses+='name2@dom.com'
    $smtpAddresses+='name3@dom.com'

    $fieldValue=[string]::Join('|',$smtpAddresses)


    ¯\_(ツ)_/¯

    Tuesday, October 15, 2013 12:34 PM
  • For ProxyAddresses just do this

    $props=@{
          ... object items
          ProxyAddresses=[string]::Join('|',$user.proxyAAddresses)
          .... other items
    }


    ¯\_(ツ)_/¯

    • Proposed as answer by Simon Wahlin Tuesday, October 15, 2013 12:40 PM
    Tuesday, October 15, 2013 12:37 PM
  • The header for Format-Table and Export-CSV is based on the first item in the array. If you output the item which has most columns (this works since you only add columns) first it will work. Based on your example:

    $myArray[2,0,1,3,4] | ft $myArray | sort {($_ | Get-Member -MemberType NoteProperty).Count} -Descending | Export-Csv -NoTypeInformation -Path $env:TEMPC:\test.csv



    • Edited by Dirk_74 Tuesday, October 15, 2013 2:38 PM
    • Marked as answer by james_stap Wednesday, October 16, 2013 1:09 PM
    Tuesday, October 15, 2013 2:37 PM
  • Note that when building objects it is more reliable to use this method.  It is also easier.

    $properties=@{
         Colums1=$null
        Colums1=$null
        Colums1=$null
        Colums1=$null
        Colums1=$null
    }

    After that we assign values to the hash

    $properties.Column1='myvalue'

    After collecting all values that may exists we can generate the object.

    New-Object PsObject -Property $properties

    This will guarantee that all objects are identical.  We can also use variations depending on the kind of loop we are in.

    You can NEVER generate an object that has a variable number of properties into a CSV. The first object output will control the number and type of column in the CSV.

    An object is identified by the number and type of its properties. Objects with different properties are not of the same type.  (OOP 101)


    ¯\_(ツ)_/¯

    • Marked as answer by james_stap Wednesday, October 16, 2013 1:09 PM
    Tuesday, October 15, 2013 4:16 PM
  • Thanks for the comments from you both.

    I think I will be utilising both methods in some form or another.

    Dirk: Thanks for explaining why ft wasn't displaying all the columns, the fact it takes the first item and then proceeds from there helps with my understanding of why all were not output. I couldn't work that one out, thanks.

    From your 2 lines of code provided, I understand the second method but (showing my naivety here) what do the numbers [2,0,1,3,4] represent in your first line of code? The first method is working fine and I could utilise that, but just for my reference if you could explain the numbers that would be great.

    Jrv: Thank you for your posts as well. The join method would have worked but I was wanting to keep each of the addresses in a separate column. But I'm sure that example I will use in something else J But I like the array creation part you later posted. This also works but is there anyway of sorting the headers into a particular order? As if I use your example and modify slightly; col1 and newcol appear in the middle. I would want col1 at the beginning and newcol at the end. Is there any way to accomplish this?

    $properties=@{

         Col1=$null

         Col2=$null

         Col3=$null

         Col4=$null

         Col5=$null

    }

    $properties.col1='myvalue'

     

    $properties.newcol='another'

     

    $test = New-Object PsObject -Property $properties

    $test | ft

    Wednesday, October 16, 2013 11:30 AM
  • The numbers represent indices of the array (I saw that your example actually doesn't have an item with the index 4) that way you can output the array in slices basically this was to show you the manual way on how to get it to work and the second part is representing  a more generic solution:

    #output array elements according to the order of the listed indices 2,0,1,3
    $myArray[2,0,1,3]

    Wednesday, October 16, 2013 11:40 AM
  • Thanks Dirk. Understood.

    Wednesday, October 16, 2013 1:08 PM