psobject property set is losing order - how to retain order?

Question

I'm working with Export-Excel, and the psobject property set I am using is re-ordering itself, which makes my excel heading in the wrong order.

This is my code:

       foreach ($errCode in $ErrCodes) 
       {
          $resultHelp = [System.Collections.Generic.List[psobject]]::new() 
          Write-Host "another:"
          $err = $errCode #get each error code to lookup in mdb file
          Write-Host $err
          $resultHelp = ProcessHelpMDB -mdbLookupError $errCode -mdbFilePath $basePathFull -mdbDeviceSeries $Ver #######

          $result = [ordered] @{
                  "ScreenNumber" = ""
                  "KKey" = ""
                  "EnglishUS" = ""
                  "PictureID" = ""
                  "ImageFound" =""
                  "ImageFileName" = ""
          } #this didn't do anything
          #loop thru results from Help MDB file for $errCode
          ###this loses order:
          $result = foreach($row in $resultHelp.GetEnumerator()) #this is working for each row
          {
              if($row -ne $True) #not sure why it adds true at the top
              {
                Write-Host $row.TextID #prints HELP_JAM; this is key for kapptext.mdb
                #lookup value from kapptext.mdb
                Write-Host $($row.TextID) #prints nothing but looks ok in next function
                $longText = ProcessK_MDB -mdbLookupstring $($row.TextID) #gives English-US from db for parameter given, which is long error instruction
                #export data found above to excel. 
                #### minimal example shows how get each row data since it's part of issue
                #get the data ready to put in spreadsheet
                $result = New-Object psobject -Property @{
                    ScreenNumber = $($row.ScreenNumber)
                    KKey = $($row.TextID)
                    EnglishUS = $($longText)
                    PictureID = $($row.PictureID)
                    ImageFound = ""
                    ImageFileName = ""
                } 
              } #if not true
          } #foreach row $resultHelp
          #######I think something needs to change in data structure above...not sure what
          $date = (Get-Date).tostring("MM-dd-yyyy") 
          [System.String] $help_spreadsheet_File = "COMPONENT MAP HELP_",$($date),".xlsx"
          $out_path = '\\company.net\EndToEnd\ComponentMaps'
          $outFilePath = Join-Path -Path $out_path -ChildPath $help_spreadsheet_File
           #write to dated file at out_path location
           #export content to spreadsheet
           $xlsx = $result | Export-Excel -Path $outFilePath -WorksheetName $errCode -Autosize -AutoFilter -FreezeTopRow -BoldTopRow  -PassThru # -ClearSheet can't ClearSheet every time or it clears previous data  ###seems to have issue over-writing each row below header
          $ws = $xlsx.Workbook.Worksheets[$errCode]
          $ws.Dimension.Columns  #number of columns
          $ws.Dimension.Rows     #number of rows
          $ws.Row(1).Height
          $ws.Row(1).Height = 25
          $ws.Column(5).Width
          $ws.Column(5).Width = 50
          Close-ExcelPackage $xlsx
          Write-Host "Break when done with first errCode $($errCode)" #50-9

This is what $resultHelp looks like:

[0] $true (I'm not sure why it puts true at the top)
[1] @(HelpCode=9;ScreenNumber=1;TextID=HELP_...JA;PictureID=HELP_...RIB)
[2] @(HelpCode=9;ScreenNumber=2;TextID=HELP_...ROLL;PictureID=HELP_...ROLL)
[3] @(HelpCode=9;ScreenNumber=3;TextID=HELP_...EDGE;PictureID=HELP_...UT)
...

The output looks like this (re-ordering itself):

ImageFound  ScreenNumber  ImageFileName   KKey   EnglishUS   PictureID
(data here)

I was looking at column ordering and it didn't change the order output to the spreadsheet. I also tried adding [ordered] here,

$result = New-Object psobject -Property [ordered]@{

but it had error message (and I couldn't find close enough example to fix it)

New-Object : Cannot bind parameter 'Property'. Cannot convert the "[ordered]@" value of type "System.String" to type "System.Collections.IDictionary".

That should've worked, as far as casting the ordered type to the hashtable. Can your try using the `[Pscustomobject]@{..}` type accelerator instead? Should save it in order — Abraham Zinala, Jan 26 '22 at 17:06

score 6 · Accepted Answer · answered Jan 26 '22 at 17:10

6

You're passing a hashtable to New-Object -Property and hashtables don't preserve input order.

Change this:

$result = New-Object psobject -Property @{
    ScreenNumber = $($row.ScreenNumber)
    KKey = $($row.TextID)
    EnglishUS = $($longText)
    PictureID = $($row.PictureID)
    ImageFound = ""
    ImageFileName = ""
}

To:

$result = New-Object psobject -Property $([ordered]@{
    ScreenNumber = $($row.ScreenNumber)
    KKey = $($row.TextID)
    EnglishUS = $($longText)
    PictureID = $($row.PictureID)
    ImageFound = ""
    ImageFileName = ""
})

The reason it doesn't work unless you wrap the [ordered]@{...} expression in $() is that PowerShell employs different rules for parsing command arguments

answered Jan 26 '22 at 17:10

Mathias R. Jessen

157,619
12
148
206

1

wouldn't using a `[PSCustomObject]` call to build the object be somewhat better? no need for the ordered hashtable ... and it is generally faster, simpler, and [to me] it reads as something more "direct" than the `New-Object` structure. – Lee_Dailey Jan 26 '22 at 18:38
1

@Lee_Dailey That would be a perfectly good choice (and my personal preference), but OP was already exploring passing an ordered dictionary to `New-Object` so I thought "better walk them all the way to the edge of the water" instead of imposing my own preferences :) – Mathias R. Jessen Jan 26 '22 at 18:40
ah! thank you for the rationale ... i appreciate it! [*grin*] – Lee_Dailey Jan 26 '22 at 18:41

psobject property set is losing order - how to retain order?

1 Answers1