Wednesday, 3 July 2013

Avoid Square Brackets in PowerShell Property Names

It should have been a pretty simple task. I had a bunch of CSV data files that were extracts from a SQL Server database. They had no header rows, but I did have the SQL definitions of the table. That meant I could use SED on the table definitions to generate a separate text file with just the column names and use the -Header switch of Import-Csv to fill in the gaps.

[Id]
[FirstName]
[LastName]
. . .

As a test I picked out the first row to make sure my PowerShell one-liner was correct. It looked like this:-

PS C:\> import-csv -header (get-content Header.txt) Customers.txt | select '[FirstName]','[LastName]' -first 1

But my output was essentially empty. I had the columns correctly selected, but no data. It looked like this:-

[FirstName]     [LastName]
-----------     ----------

I checked all the obvious things, like the quoting around the property names, the property names themselves, what Get-Content was returning, etc.. But as far as I could see it was correct. Having done this kind of thing a million times before, the only variable I could see was the square brackets surrounding the column names. But they were coming out in the output, and a quick check with good all reliable Get-Member shows they really are there:-

PS C:\Temp> import-csv -header (get-content Header.txt) Customers.txt | get-member


   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
[FirstName] NoteProperty System.String [FirstName]=[FirstName]
[LastName]  NoteProperty System.String [LastName]=[LastName]

So that pretty much leaves some strange translation going on in the final Select-Object part of the pipeline. Sadly my Google Fu hasn’t found a definitive answer (although I’ve seen others about square brackets in pathnames) so I’m hoping a passer by will eventually put me straight.

P.S. If you want to play along at home then the following is the issue distilled down:-

[FirstName],[LastName]
Chris,Oldwood

C:\> PowerShell "import-csv Customers.txt | select '[FirstName]','[LastName]'"

4 comments:

  1. Yuck, that's ugly. :)

    The only workaround I could see using Select-Object is this one:

    ... | Select-Object @{name='[FirstName]';expression={$_.'[FirstName]'}},@{name='[LastName]';expression={$_.'[LastName]'}}

    If you have a lot of properties you could generate that property collection a little easier first using a foreach loop, but it's still overkill for an annoying bug, and the point you make is valid: you should never use square brackets in property names (or spaces or round brackets or anything other than alphanumeric characters for that matter).

    Kirk out.

    ReplyDelete
  2. Sorry about that - It's a bug. You should file a report over on http://connect.microsoft.com/

    Here is something interesting:
    import-csv Customers.txt | select *FirstName],*LastName]

    Jeffrey Snover[MSFT]
    Distinguished Engineer and Lead Architect for Windows Server and System Center Datacenter

    ReplyDelete
  3. A bug has been filed:-

    https://connect.microsoft.com/PowerShell/feedback/details/792986/square-brackets-in-property-names-not-handled-correctly-by-select-object

    ReplyDelete
  4. A more precise cleaner workaround might be to just escape the first opening bracket, i.e.

    import-csv Customers.txt | select '`[FirstName]', '`[LastName]'

    I'm not 100% sure this is a bug, as I'm assuming it is the standard wildcard expansion which allows you to do cool stuff like:
    Get-ChildItem | fl *name

    On the file system cmdlets they put a LiteralPath switch in to bypass wildcard expansion, but I'm not 100% sure that is the right way to go here either.

    A really trivial search didn't show up a standard PowerShell supplied way to escape wildcards similar to [RegEx]::Escape, which might be a reasonable workaround to pass your names through. You could write your own, there aren't many wildcard characters, see http://msdn.microsoft.com/en-us/library/aa717088(v=vs.85).aspx.

    I've voted for your bug as it hit us as well, but I actually think this is a design feature even with the weight of Jeffrey behind it. When thinking about it, I actually probably would prefer the functionality left in.

    ReplyDelete