powershell: import-csv | get-member: sort column names / property names based on (original) position. rename colnames?
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}}
knb
I do web development, system- and database administration in a small group at a research institute. Active here on StackExchange.com in my spare time.
Updated on June 04, 2022Comments
-
knb almost 2 years
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[$_] } }
-
knb almost 13 yearsThis 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.
-
Emiliano Poggi almost 13 yearsIs this pertaning to your first question? Is that you are trying to do with the custom sorting function? See my edit.
-
knb almost 13 yearsthe "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?
-
Emiliano Poggi almost 13 yearsSorry but I think I would need some more details or example of your code. At the moment, to me, my answer answers :)
-
Emiliano Poggi almost 13 yearsAttention, because hashtable items are characterized by not being ordered at all. How do you use this with
get-member
then? I'm courious now :) -
knb almost 13 years$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.
-
Allen about 4 years# 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 about 4 yearsAlthough I suspect these nested loops in my comment example can be done way more efficiently. Any truly elegant suggestions?