PowerShell - Reference a column or property by ID or position (anything but name)

Question

Curious if there is any hidden way to reference a property without typing out the property name. I know I could create a bunch of variables to use in place of the full name, but I'm surprised the data's headers aren't 'Gettable' by their relative position.

I'm working with CSVs right now specifically, but here's an example of what I'm trying to do - let's say I want to update a bunch of fields, but typing ."First Name" sucks...Is there anyway to substitute something like $_.Column2?

$data = Import-CSV .\data.csv | %{If($_."First Name" -eq "Bob"){$_."First Name" = "Robert"};$_}

Example data.csv

Employee's ID First Name Last Name Phone Number
------------- ---------- --------- ------------
12345         Bob        Jackson   555-555-5555
67891         Jessie     Jackson   123-456-7891

I want to make it clear that I don't have any problems with Bob, but I can't find much out about PSObject Properties and I figured there might be some hidden gem in there that I don't know about. I also know that this would make longer scripts harder to read and more difficult to troubleshoot in the future, but for quick things it would be helpful.

As an aside, here are a few of the things I've considered implementing -

Creating variables for each column and using $1, $2, etc.
Import the data using different headers (like col1, col2, etc.) and then fix the headers when I'm done

Why use import-CSV at all? Couldn't you just use get-content and "split" each line? Ex: ("1234,john,721-0000" -split ",")[1] — ExcèsRefusé, Aug 22 '21 at 00:33
If you want the 2nd of the header names I'd go with `$data[0].psobject.Properties.Name[1]` — Olaf, Aug 22 '21 at 01:27
@Daniel - that seems to just give me `InvalidOperation: You cannot call a method on a null-valued expression.` and I'm not sure what it is doing so I can't troubleshoot. It does seem like @Olaf's suggestion will work, but obviously typing something like "First Name" is going to be quicker than that and probably marginally quicker than the system looking up the name of the header each time — immobile2, Aug 22 '21 at 02:12
@ExcèsRefusé - There are a number of reasoning I'm using import-csv; but primarily because I'm working with CSVs and after my updates I'm exporting them back to CSVs. But for this particular question import-csv or get-content doesn't really matter, I was just wondering if anyone knew of a trick to reference a column without using its name. It is certainly possible to use Split for a few things I hadn't thought of now that I'm looking at some example output, but nothing that actually answers the question per se — immobile2, Aug 22 '21 at 02:26
@ExcèsRefusé Thinking I might have overlooked the simplicity in what you could have been getting at. One of the advantages I like about Import-CSV is that I can refer to the data of each row by it's column name; but then I'm here wondering if there is a way to refer to it by it's relative position instead of name. So yeah, using Get-Content vs. Import-CSV would probably be better if I don't want to use the Header Names — immobile2, Aug 22 '21 at 20:57

mklement0 · Accepted Answer · 2022-03-11T00:22:34.627

but typing ."First Name" sucks...Is there an yway to substitute something like $_.Column2?

@($_.psobject.Properties)[1] is the most succinct way to refer to the second property (column) positionally^[1], using the instrinsic .psobject member - add .Name to get that property's name, and .Value to get its value; alternatively, use $_.psobject.Properties.Name[1] and $_.psobject.Properties.Value[1].^[2]

In other words: there is no built-in concise way to refer to an object's properties positionally.

If you do know the property name of interest and simply want to avoid typing it repeatedly, you can use an aux. variable:

Import-CSV .\data.csv | ForEach-Object {
  if ($_.($n='First Name') -eq 'Bob') { $_.$n = 'Robert' }
  $_
}

Note how the assignment $n='First Name' is used as an expression, by enclosing it in (...), which means that the value being assigned becomes the expression's output value, which in this case serves as a property name.

If you want concise positional access to all properties (columns), create an array containing all property names on demand, and use (positionally) indexed access to it to indirectly get the property name of interest positionally:

$n = $null
Import-CSV .\data.csv | ForEach-Object {
  if ($null -eq $n) { $n = $_.psobject.Properties.Name }
  if ($_.($n[1]) -eq 'Bob') { $_.($n[1]) = 'Robert' }
  $_
}

^{[1] $_.psobject.Properties returns a collection of type [System.Management.Automation.PSMemberInfoIntegratingCollection[System.Management.Automation.PSPropertyInfo]], which only supports a key-based indexer (the property name, e.g., $_.psobject.Properties['First Name']), not a positional one. Applying @(...), the array-subexpression operator enumerates the collection and collects the results in an array (a regular PowerShell array, which is of type [object[]]), to which positional indexing can then be applied.}

^{[2] This approach applies member-access enumeration first, which returns an array of the property names / values, to which positional indexing can be applied.}

PowerShell - Reference a column or property by ID or position (anything but name)

1 Answers1