How to reference a CSV column by a name which includes spaces and other punctuation?

Question

Working with Google Contacts as CSV and looking to ensure that the e-mail value is lower case, the default name for the e-mail address column is "E-Mail 1 - Value" and so looking to iterate only that column. However, the punctuation is causing a problem:

PS C:\Users\Nick\Desktop>
PS C:\Users\Nick\Desktop> $csv | Get-Member -MemberType Properties


   TypeName: CSV:System.Management.Automation.PSCustomObject

Name                 MemberType   Definition
----                 ----------   ----------
Additional Name      NoteProperty string Additional Name=
Additional Name Yomi NoteProperty string Additional Name Yomi=
Billing Information  NoteProperty string Billing Information=
Birthday             NoteProperty string Birthday=
Directory Server     NoteProperty string Directory Server=
E-mail 1 - Type      NoteProperty string E-mail 1 - Type=
E-mail 1 - Value     NoteProperty string E-mail 1 - Value=Conwayfp@Shaw.Ca
Family Name          NoteProperty string Family Name=Pauls
Family Name Yomi     NoteProperty string Family Name Yomi=
Gender               NoteProperty string Gender=
Given Name           NoteProperty string Given Name=
Given Name Yomi      NoteProperty string Given Name Yomi=
Group Membership     NoteProperty string Group Membership=
Hobby                NoteProperty string Hobby=
Initials             NoteProperty string Initials=
Language             NoteProperty string Language=
Location             NoteProperty string Location=
Maiden Name          NoteProperty string Maiden Name=
Mileage              NoteProperty string Mileage=
Name                 NoteProperty string Name=Conway
Name Prefix          NoteProperty string Name Prefix=
Name Suffix          NoteProperty string Name Suffix=
Nickname             NoteProperty string Nickname=
Notes                NoteProperty string Notes=
Occupation           NoteProperty string Occupation=
Phone 1 - Type       NoteProperty string Phone 1 - Type=Mobile
Phone 1 - Value      NoteProperty string Phone 1 - Value=2502034036
Phone 2 - Type       NoteProperty string Phone 2 - Type=
Phone 2 - Value      NoteProperty string Phone 2 - Value=
Photo                NoteProperty string Photo=
Priority             NoteProperty string Priority=
Sensitivity          NoteProperty string Sensitivity=
Short Name           NoteProperty string Short Name=
Subject              NoteProperty string Subject=
Yomi Name            NoteProperty string Yomi Name=


PS C:\Users\Nick\Desktop>
PS C:\Users\Nick\Desktop> $csv | ForEach-Object {$_.'E-mail 1 - Value'.(Get-Culture).TextInfo.ToLower($_.'E-mail 1 - Value')  }

which is returning an error of:

At line:1 char:24
+ ... ach-Object {$_.'E-mail 1 - Value'.(Get-Culture).TextInfo.ToLower($_.' ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:1 char:24
+ ... ach-Object {$_.'E-mail 1 - Value'.(Get-Culture).TextInfo.ToLower($_.' ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:1 char:24
PS C:\Users\Nick\Desktop>

which I take to mean that the column isn't being referenced correctly. In short, how are spaces escaped in this situation?

see also:

How to convert a specific CSV column to TitleCase with PowerShell from the REPL console?

Your syntax is wrong, change to: $_.'E-mail 1 - Value' = (Get-Culture).TextInfo.ToLower($_.'E-mail 1 - Value') — dugas, Jun 23 '23 at 19:11

mklement0 · Accepted Answer · 2023-06-23T19:22:38.597

$_.'E-mail 1 - Value'

This - i.e. the use of quoting - is the correct way to refer to property names that contain special characters such as spaces.

You can even use expressions to calculate property names, using (...), the grouping operator; e.g.:

# -> 42
([pscustomobject] @{ 'Foo 1' = 42 }).('Foo' + ' ' + '1')

Using (...) to calculate a property name is what you mistakenly did when you tried:

$_.'E-mail 1 - Value'.(Get-Culture).TextInfo.ToLower(...)

This tries to use the (stringified) output from a Get-Culture call as a property name, then tries to access the resulting value's .TextInfo property, and tries to call the .ToLower() method on that.

Since everything starting with .(Get-Culture) evaluates to $null - due to the non-existence of such properties - the method call failed, because you cannot call methods on $null.

What you are looking for is just:

(Get-Culture).TextInfo.ToLower($_.'E-mail 1 - Value')

(To assign the result back to the property, place $_.'E-mail 1 - Value' = before the expression above.)

That is, you obtain the current culture's text-formatting information and call its method for lowercasing, passing in the property value of interest.

Note, however, that you don't need an explicit reference to the current culture to lowercase based on that culture's rule: simply calling .ToLower() directly on a string is sufficient:

$_.'E-mail 1 - Value'.ToLower()

score -1 · Answer 2 · answered Jun 23 '23 at 18:59

-1

It's not well-documented, but if you search hard enough, you'll find that you can reference a member whose name contains spaces as $foo."Member Name With Spaces". I consider this to be a bad practice on general principles, however; if I know I'm going to be passing a CSV to a PowerShell script or cmdlet, I'll make an effort to get the column names to not have spaces or other annoyance characters.

answered Jun 23 '23 at 18:59

Jeff Zeitlin

9,773
2
21
33

Note that the question already shows awareness of the need to quote (`$_.'E-mail 1 - Value'`) - the real problem lies elsewhere. (I didn't down-vote, but in light of the foregoing, perhaps converting this to a _comment_ is the better approach.) – mklement0 Jun 23 '23 at 19:20

How to reference a CSV column by a name which includes spaces and other punctuation?

2 Answers2