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