powershell: import-csv | get-member: sort column names / property names based on (original) position. rename colnames?

Question

The get-member commandlet returns NoteProperties always sorted alphanumerically. See sample output below.

From import-csv I received an array in memory, now I want get-member to sort the member names by original position rather than its alphanumerical value.

The original sequence is visible in the $_.psextended.Definition string ( column names joined by commas)

I cannot in-place edit the property names, as it's read-only. As I workaround I tried to prepend a numeric prefix to the column name, see code below

Any better ideas? I don't want to in-place edit the original data file.

 $content = (import-csv -delimiter "`t" $infile_abs  );
 $colnames = $content | get-Member |  where-object {$_.MemberType -eq "NoteProperty"} ; #| out-gridview; 
 $cols = $content | get-Member -force | where-object {$_.Name -eq "psextended"} ; 
 echo($cols.Definition -replace "psextended" , "");

 $i = 0;
 $colnames| sort-object  -descending |  foreach-object { 
   $i++ ; 
   $cn = [string]::Format( "{0:00}_{1}",   $i, $_.Name )  ;
   Write-Host  $cn
 };

Sample Output of psextended

{File Name, Label, ObsDateTime, Port#, Obs#, Exp_Flux, IV Cdry, IV Tcham, IV Pressure, IV H2O, IV V3, IV V4, IV RH}

Output of $colnames = $content | get-Member | out-gridview;

Exp_Flux    NoteProperty    System.String Exp_Flux=0,99 
File Name   NoteProperty    System.String File Name=xxx-11040   
IV Cdry NoteProperty    System.String IV Cdry=406.96    
IV H2O  NoteProperty    System.String IV H2O=9.748  
IV Pressure NoteProperty    System.String IV Pressure=100.7 
IV RH   NoteProperty    System.String IV RH=53.12   
IV Tcham    NoteProperty    System.String IV Tcham=16.19    
IV V3   NoteProperty    System.String IV V3=11.395  
IV V4   NoteProperty    System.String IV V4=0.759   
Label   NoteProperty    System.String Label=5m  
Obs#    NoteProperty    System.String Obs#=1    
ObsDateTime NoteProperty    System.String ObsDateTime=2011-04-04 13:19:37   
Port#   NoteProperty    System.String Port#=1

EDIT: (No answers yet)

Here is a custom sorting function, now I need to tell Get-Member to use this sorting function. How to do this in a pipeline?

 #$orig_seq = $cols.Definition -replace "psextended", "" -replace "[{}]", "";
 $orig_seq = "File Name, Label, ObsDateTime, Port#, Obs#, Exp_Flux, IV Cdry, IV Tcham, IV Pressure, IV H2O, IV V3, IV V4, IV RH";

 echo $orig_seq;
 #exit;

  &{

   $byPos= @{};
   $i = 0; 
   $orig_seq.Split(",") | % { $byPos[$i++] = $_.trim()}
   $order = ([int[]] $byPos.keys) | sort
   #$order | %{ ([string]::Format( "{0} => {1}",  $_, $byPos[$_])) }
   $order | %{ $byPos[$_] }

 }

Excuse the stupid question, but why do you want to have $content | Get-member ? why don't you just $content | out-gridview ? — JPBlanc, Apr 29 '11 at 16:08
@empo: No, I did not read your answer before I posted this. In any case, my question is interesting enough in itself, hence I created this new stackoverflow question. @JPBLanc: I want to process csv data, piping them to excel in the original order. get-member is needed to get the header row, but get-member rearders the column names. Someone else will use/consume the excel files (add formulas, diagrams etc) — knb, Apr 29 '11 at 21:16
I'm not sure to understand your sorting function...where `$orig_seq` come from? why are you using a script block? what's `$order`? — Emiliano Poggi, Apr 29 '11 at 21:55

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

I found the answer in question PSCustomObject to Hashtable

The in-memory data from import-csv cmdlet is a PSCustomObject.

Its properties (column names) can be fetched in the original order with this code

 #fetch in correct order
 $content.psobject.properties |
 # do something with the column names
 Foreach { $ht2["$($_.Name)"] =  $_.Value }

Properties can be renamed this way, see

http://powershell.com/cs/blogs/tips/archive/2009/05/08/renaming-object-properties.aspx

dir | Select-Object @{Name='FileName'; Expression={$_.Name}}

edited May 23 '17 at 12:05

Community

1
1

answered May 02 '11 at 15:42

knb

9,138
4
58
85

Attention, because hashtable items are characterized by not being ordered at all. How do you use this with `get-member` then? I'm courious now :) – Emiliano Poggi May 02 '11 at 19:36
$content.psobject.properties returns the correct order. I left the right part of the pipe there because it was from the helpful posting, and it shows how to get to the prop-name and to its value. Fiddling with get-member is no longer required. I'll update my answer. – knb May 02 '11 at 21:41
# and to show using this to get the columns and values from the csv $path = "E:\Scratch\File_Names_Extracted.csv" $csv = Import-Csv -path $path -Delimiter "|" $ith = 0; foreach ($line in $csv) { foreach ($head in $line | Select-Object ) { foreach ($column in $head.psobject.properties) { $column.Name + " " + $column.Value | Format-List; } } } – Allen Feb 28 '20 at 14:29
Although I suspect these nested loops in my comment example can be done way more efficiently. Any truly elegant suggestions? – Allen Feb 28 '20 at 14:38

score 0 · Answer 2 · answered Sep 08 '14 at 10:17

I approached this in a slightly different way when processing a CSV file:

# Create a stream object where $filepath is the CSV file you're processing
$stream = [System.IO.File]::Open($filepath, [System.IO.FileMode]::Open)

# Create a reader of the stream, yes there are more efficient ways to do this
$reader = New-Object -typename System.IO.StreamReader -argumentlist $stream

# Output the first line to the target CSV file you're processing then move on...
$reader.ReadLine() | Out-File $outpath -Encoding "ASCII"

Less 'powershelly', but easy to achieve...

Emiliano Poggi · Answer 3 · 2011-05-02T11:53:03.710

0

Just a trace:

$myorder = $orig_seq.split(",")

foreach $myobject in $myobjects {

 $myorder | % {get-member -name $_ -inputobject $myobject}

}

EDIT: get sorted values

$myorder = $orig_seq.split(",")

foreach $myobject in $myobjects {

 $mysortedvalues | ? {$myobject.name -eq $_}

}

Obviously this is useful and feasable if you now $mysortedvalues in advance.

edited May 02 '11 at 11:53

answered Apr 29 '11 at 14:38

Emiliano Poggi

24,390
8
55
67

This puts property names in a particular order. Assume the object I'm interested is an array of objects where each item has a single Property called "Name" which has certain values. I want those values returned in a particular order. – knb May 02 '11 at 11:20
Is this pertaning to your first question? Is that you are trying to do with the custom sorting function? See my edit. – Emiliano Poggi May 02 '11 at 11:50
the "property names" in the title of this post refer to the property names returned by import-csv. these are in the correct order. However, when get-member gets applied, they are stuffed into an array and returned always sorted by value. How to prevent get-member to do this? – knb May 02 '11 at 12:02
Sorry but I think I would need some more details or example of your code. At the moment, to me, my answer answers :) – Emiliano Poggi May 02 '11 at 15:39

powershell: import-csv | get-member: sort column names / property names based on (original) position. rename colnames?

3 Answers3