Header, header, wherefore art thine fields?

Header, header, wherefore art thine fields?

  •  
  •  
  •  
  •  
  •  
  •  
Header, header, wherefore art thine fields?
Rate this post

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.

Or, if you have a really large CSV that you don’t want to import all the way, you can do just the first line with Get-Content:

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

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!

 

 

 

Reader Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.