Translating a CSV Header

Let’s face it! PowerShell and CSV are a match made in heaven!
Remember the good ol’ vbs days importing a csv file? Yeah… Good times! 😉

Whenever I need to do any bulk Active Directory activity, csv is my choice for source input.
In an ideal situation the headers should reflect the cmdlet parameter, you know, for splatting purposes. Trust me once you splat, you’ll never go back!!! (We get it Urv you’re a huge fan of splatting!!!)

I work in the Netherlands where Dutch is the common language. I’ve hinted in the past that I’d love to have my csv file with the right header in place. “Now see here Mr. Strachan (Oh oh, I dun messed up now…)”. “We could add a comment to the cell in xlsx that could show what the header means in dutch”, I muttered as a last defense… Yeah, ain’t gonna happen anytime soon…

What I did in the past was add a new line in xlsx to line up the right header entry to the correct cmdlet parameter, manually (don’t judge me). “Well, if you promise to keep the headers consistent then I’ll think of something”. Ok so here’s the something I thought about…

Import the csv and then create [PSCustomObjects] as you go along using the pipeline. Pretty straightforward actually. I’ve noticed that when scripting I tend to shy away from the pipeline. I usually gather everything first and then process.

UserID nieuw	LoginNaamNieuw	Personeelsnummer	VoorNaam	AchterNaam	Afdeling	Bedrijf	PostCode	StraatNaam	Stad
150131	150131	150131	John	Wayne	IT	Acme	1222 XX	Elmstreet 26	Sciencefiction
150141	150141	150141	Jane	Doe	HR	Acme	1222 XX	Elmstreet 26	Sciencefiction
150211	150211	150211	Jack	Swift	IT	Acme	1222 XX	Elmstreet 26	Sciencefiction
"@ |
ConvertFrom-Csv -Delimiter "`t" |
foreach {
      SamAccountName =  $_.'UserID nieuw'
      Name = $_.’UserID nieuw’
      EmployeeID = $_.Personeelsnummer
      GivenName = $_.Voornaam
      SurName = $_.Achternaam
      DisplayName = "$($_.VoorNaam) $($_.Achternaam)"
      Department =  $_.Afdeling
      Company = $_.Bedrijf
      Postalcode = $_.PostCode
      StreetAddress = $_.StraatNaam
      City = $_.Stad
} |
Out-GridView -Title "Imported CSV with Different header  $(Get-date)"

I usually do all my pre formatting in Excel (been doing that for years). I’d add an extra column for DisplayName and do a concatenate in Excel. I try not to reformat data in my scripts. that way the script could remain a blackbox so to speak. Companies usually have different naming conventions. DisplayName, for instance, could be one of the following naming conventions:

  • “GivenName SurName”
  • “SurName GivenName
  • “GiveName Initials SurName”

By doing it in Excel it saved me the hassle of revisioning my scripts every time. I just need to know what it is. I decided to do it on the fly this time. I learned a new acronym recently: The DRY principal (Don’t repeat yourself)… Seems legit…

Instead of out-gridview you could now just as easily created the users using New-ADUser cmdlet. Just make sure the mandatory parameter is in place.

Well I caved, but in the process I got reacquainted with the pipeline, so I’m considering it a win! It’s all about the silver lining eh? 😉




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s