With the PowerShell Summit registration deadline rapidly approaching I wanted to see how registrations were going. I can down load a CSV file from the event website that lists attendees.
Great. Just need to do some sorting and grouping and I’m golden.
I’m running PowerShell 5.0 on Windows 10 Insider build 14271
First off I discovered that the headers of the CSV file contain [] i.e. they look like this for example:
[UserName]
[REGISTRATIONSTATUS]
[AttendeeType]
[ConfirmationCode]
Sorting by attendee type
Import-Csv -Path C:\test1\AttendeeReport.csv |
Sort-Object -Property [AttendeeType]
works but if I add a check for complete registrations
Import-Csv -Path C:\test1\AttendeeReport.csv |
Where-Object [REGISTRATIONSTATUS] -eq ‘Complete’|
Sort-Object -Property [AttendeeType]
I get nothing back.
Import-Csv -Path C:\test1\AttendeeReport.csv |
Where-Object ‘[REGISTRATIONSTATUS]’ -eq ‘Complete’|
Sort-Object -Property [AttendeeType]
Is no better. You need to revert to old style where-object syntax
Import-Csv -Path C:\test1\AttendeeReport.csv |
Where-Object {$_.'[REGISTRATIONSTATUS]’ -eq ‘Complete’}|
Sort-Object -Property [AttendeeType]
Now I want to group on attendee type
PS> Import-Csv -Path C:\test1\AttendeeReport.csv |
>> Where-Object {$_.'[REGISTRATIONSTATUS]’ -eq ‘Complete’}|
>> Sort-Object -Property [AttendeeType] |
>> Group-Object -Property [AttendeeType] -NoElement
Group-Object : Wildcard characters are not allowed in "[AttendeeType]".
At line:4 char:1
+ Group-Object -Property [AttendeeType] -NoElement
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Group-Object], NotSupportedException
+ FullyQualifiedErrorId : ExpressionGlobbing2,Microsoft.PowerShell.Commands.GroupObjectCommand
This partially works
Import-Csv -Path C:\test1\AttendeeReport.csv |
Where-Object {$_.'[REGISTRATIONSTATUS]’ -eq ‘Complete’}|
Sort-Object -Property [AttendeeType] |
Group-Object -Property ‘`[AttendeeType`]’ -NoElement
But just gives me a count of the total number of attendees.
I tried a number of ways of dealing with the [] in the headers and therefore in the property names. In the end I decided it was going to be easier to completely reset the headers in the csv file:
Import-Csv -Path C:\test1\AttendeeReport.csv -Header ‘UserName’, ‘REGISTRATIONSTATUS’, ‘AttendeeType’, ‘ConfirmationCode’, ‘PaymentAuthorization’, ‘RegisteredByEmail’, ‘PromotionCode’, ‘BioName’, ‘BioTitle’, ‘BioEmail’, ‘Created’, ‘Updated’, ‘LastName’, ‘Dietary’, ‘Alumni’, ‘FirstName’, ‘Twitter’, ‘BestEmail’
This means I have to skip the first record because it looks like this:
UserName : [UserName]
REGISTRATIONSTATUS : [REGISTRATIONSTATUS]
AttendeeType : [AttendeeType]
ConfirmationCode : [ConfirmationCode]
PaymentAuthorization : [PaymentAuthorization]
RegisteredByEmail : [RegisteredByEmail]
PromotionCode : [PromotionCode]
BioName : [BioName]
BioTitle : [BioTitle]
BioEmail : [BioEmail]
Created : [Created]
Updated : [Updated]
LastName : [LastName]
Dietary : [Dietary]
Alumni : [Alumni]
FirstName : [FirstName]
Twitter : [Twitter]
BestEmail : [BestEmail]
My grouping script is now much simpler
Import-Csv -Path C:\test1\AttendeeReport.csv -Header ‘UserName’, ‘REGISTRATIONSTATUS’, ‘AttendeeType’, ‘ConfirmationCode’, ‘PaymentAuthorization’, ‘RegisteredByEmail’, ‘PromotionCode’, ‘BioName’, ‘BioTitle’, ‘BioEmail’, ‘Created’, ‘Updated’, ‘LastName’, ‘Dietary’, ‘Alumni’, ‘FirstName’, ‘Twitter’, ‘BestEmail’ |
Select-Object -Skip 1 |
Where-Object REGISTRATIONSTATUS -eq ‘Complete’|
Sort-Object -Property AttendeeType |
Group-Object -Property AttendeeType –NoElement
If I come across other CSV files with [] in the headers I’m going to go for immediate replacement of the headers as the way to get the job done.