Monthly Archive

Categories

CSV file with [] in headers

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.

Comments are closed.