Share via


Header, header, wherefore art thine fields?

Today, I got it in my head that I wanted to create a script that would accept CSV input.  In so doing, I wanted to make sure the CSV passed some sort of validation so that I didn't end flooding a screen with errors, because nobody likes that.

So, I fiddled around for a while, and came up with (what I think) is a pretty nifty solution.  Let's say you have a requirement that your input file has three columns:

FirstName,MiddleName,LastName
Steve,Grant,Rogers
Peter,Benjamin,Parker

And you want to be able to ensure that at a minimum, those three columns exist.  The secret sauce lies in the NoteProperty exposed when you import the CSV.

 PS C:\temp> $Users = Import-Csv UsersList.txt
PS C:\temp> $Users | Get-Member
PS C:\temp> $Users | 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 string Firstname=Steve
LastName NoteProperty string LastName=Rogers
MiddleName NoteProperty string MiddleName=Grant

Oh, looky! We have NoteProperty names that matches our CSV headers! Woot!

So, we can create a nifty regular expression to test for the presence of those Note Property values!

 $RequiredColumns = "firstname","middlename","lastname"
$RegexMatch = "^(?i)(?=.*\b" + (($RequiredColumns | foreach {[regex]::escape($_)}) -join "\b)(?=.*\b" ) + "\b).*`$"

And the resulting value:

 PS C:\temp> $RegexMatch
^(?i)(?=.*\bfirstname\b)(?=.*\bmiddlename\b)(?=.*\blastname\b).*$

So, put it together:

 PS C:\temp> $RequiredColumns = "firstname", "middlename", "lastname"
PS C:\temp> $RegexMatch = "^(?i)(?=.*\b" + (($RequiredColumns | foreach { [regex]::escape($_) }) -join "\b)(?=.*\b") + "\b).*`$"
PS C:\temp> $NoteProperties = ($Users | Get-Member -MemberType NoteProperty).Name -join ","
PS C:\temp> If ($NoteProperties -match $RegExMatch) { "Header appears to be correct."}
Header appears to be correct.

You can even test it by adding either another column to $RequiredColumns or by taking away a NoteProperty:

 PS C:\temp> $NoteProperties = $NoteProperties.Replace(",MiddleName","")
PS C:\temp> $NoteProperties
Firstname,LastName
PS C:\temp> If ($NoteProperties -match $RegExMatch) { "Header appears to be correct."}
PS C:\temp> If ($NoteProperties -match $RegExMatch) { "Header appears to be correct."} else { "NoteProperties does not match." }
NoteProperties does not match.

I'm sure there are plenty of other ways to do it, but this one appealed to me due to its simplicity.  If you have another way, I'd love to hear about it!