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]'"
Yuck, that's ugly. :)
ReplyDeleteThe 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.
Sorry about that - It's a bug. You should file a report over on http://connect.microsoft.com/
ReplyDeleteHere 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
A bug has been filed:-
ReplyDeletehttps://connect.microsoft.com/PowerShell/feedback/details/792986/square-brackets-in-property-names-not-handled-correctly-by-select-object
A more precise cleaner workaround might be to just escape the first opening bracket, i.e.
ReplyDeleteimport-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.
Just wanted to say I've encountered the same problem in 2021. Having a pscustomobject with properties with square brackets still breaks Select-Object. I've switched to () as a simple workaround.
ReplyDeleteUnfortunately MS Connect is not longer operational, so I couldn't take a look if your feedback got anywhere.
Thanks for this article though :)