PowerShell for Windows Admins

Feb 29 2016   6:30AM GMT

CSV file with [] in headers

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Tags:
Powershell

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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: