How do You Control Column Name Position When Creating CSV Exportable Object Arrays?

Question

REFERENCE SCRIPTS:

Script 1:

$csvList = @()

$csvList += New-Object PSObject -Property @{name="test1";accountname="testuser1";mail="user1@somewhere.com"}
$csvList += New-Object PSObject -Property @{name="test2";accountname="testuser2";mail="user2@somewhere.com"}
$csvList += New-Object PSObject -Property @{name="test3";accountname="testuser3";mail="user3@somewhere.com"}
$csvList += New-Object PSObject -Property @{name="test4";accountname="testuser4";mail="user4@somewhere.com"}

$csvList | Export-Csv c:\temp\testcsv.csv -NoTypeInformation

Script 2 (added in edit to reflect extended usage):

$aTest = @()

for($x=0;$x -le 5;$x++)
{
    $aTest += New-Object PSObject -Property @{Name="test$($x)"; `
                                              AccountName="testuser$($x)"; `
                                              Mail="user$($x)@somewhere.com"}
}

$aTest | Export-Csv c:\temp\testcsv.csv -NoTypeInformation

QUESTION:

While that script creates my CSV and includes all the data I need in the correct rows, I cannot figure out how to control column position. Even though I'm ordering and adding the data by name,accountname,mail Powershell orders it by mail,name,accountname. How can I control the column order?

Note: If I do a screen dump of the contents of $csvList before the export the order has already been changed.

score 3 · Accepted Answer · answered Sep 04 '14 at 16:04

3

Each PSObject is essentially a hashtable. There is no ordering of values in a hashtable. Select-Object can reformat the order for you. Make your last line:

 $csvList | Select-Object name,accountname,mail | Export-Csv c:\temp\testcsv2.csv -NoTypeInformation

Got the idea from this forum question: Source

answered Sep 04 '14 at 16:04

Ross Presser

6,027
1
34
66

1

Worked like a charm, extra kudos for including your source ;) As soon as the 'mark as answer' timer expires I'll accept your response as the answer. In hindsight, I don't know why select didn't occur to me. – Colyn1337 Sep 04 '14 at 16:08
3

Actually, if he had done `[Ordered]@{'Name'='value';'NextName'='OtherValue'}` it would have ordered the hashtable values. – TheMadTechnician Sep 04 '14 at 16:44

mjolinor · Answer 2 · 2014-09-04T17:15:27.403

3

If you're running V4, they added a type accelerator ([PSCustomObject]) for creating PS Objects that uses an ordered hash table so the properties stay in the order they were declared in the hash literal.

$(
[PSCustomObject]@{name="test1";accountname="testuser1";mail="user1@somewhere.com"}
[PSCustomObject]@{name="test2";accountname="testuser2";mail="user2@somewhere.com"}
[PSCustomObject]@{name="test3";accountname="testuser3";mail="user3@somewhere.com"}
[PSCustomObject]@{name="test4";accountname="testuser4";mail="user4@somewhere.com"}
) | Export-Csv c:\temp\testcsv.csv -NoTypeInformation

Edit: Example using a loop to build up an ordered hash table:

foreach ($i in 1..4)
 {
   $ht = [ordered]@{}
   $ht.name = "test$i"
   $ht.accountname = "testuser$i"
   $ht.mail = "user$i@somewhere.com"
   [PSCustomObject] $ht
 }

edited Sep 04 '14 at 17:15

answered Sep 04 '14 at 16:11

mjolinor

66,130
7
114
135

+1 for a correct answer to my question. I'd only point out that this option doesn't appear to work well in a script where object data is added by foreach/for loops. – Colyn1337 Sep 04 '14 at 16:17
1

If your building up a hash table by adding entries in a loop, there is a type accelerator ([ordered]) to start with an ordered hash table - eg $ht=[ordered]@{}. The keys will stay in the order they were added to the hash table, and a PS object created from that hash table will have it's properties in that order. – mjolinor Sep 04 '14 at 16:24
I really appreciate your taking the time to append with a loop solution. I've tried to adapt your suggestion and can't seem to get the data right or I get an error that "a hash table can only be added to another hash table". I totally get that this is likely user error stemming from a lack of experience on my part, but if you could update your answer with a small example I'd love to learn from it. – Colyn1337 Sep 04 '14 at 16:49
OK. Added an example (hopefully that's what you were after. Note: the ordered hash table can also be used with New-Object if you rather use that than the [PSCustomObject] type accelerator. – mjolinor Sep 04 '14 at 17:17

How do You Control Column Name Position When Creating CSV Exportable Object Arrays?

2 Answers2