Tag Archives: translate header

Return of the splat!!!

‘Sup PSHomies? How’s it hashin’? 😛

What is a PSHomie pray tell? PSHomie (plural PSHomies) is a term of endearment used amongst PowerShell enthusiasts… Hehe…

Spreading the gospel that is PowerShell! I have my first splat disciple, he goes by the name of Ralf Kervezee. Ralf is an all-around IT guy. He recently took to PowerShell with gusto!!! Go Ralfie!!! We affectionately call him “stuiterbal” which roughly translates to “bouncy ball”. If you meet him in person you’ll understand why.

This meme kinda sums Ralf up:

hey_look_a_squirrel_shirt

Yeah that’s our Ralfie!!! 😉

I’ve been grooming Ralf to do Active Directory Migration based on PowerShell scripts. Now bear in mind Ralf is our “stuiterbal”, he doesn’t ask one question ,he asks five, and while I’m trying to answer the first he’s already moved on to something else. He enjoys figuring things out for himself, which is a good trait. I’ve seen many shamefully request ready-made scripts on the InterNet. If you find a script on the InterNet you should at least test it and know how it works, if you can’t figure it out then maybe you shouldn’t use it eh? Seem fair enough right?

So instead of giving him something ready-made, I gave him a script that helped him figure out how to go about solving his problem. I recently found something on the Net that didn’t work at first but I figured it out, and I must say I ended up appreciating the solution more. Give a man a fish… 😉

We both work at the same company, so we have a mutual CSV Header challenge. There’s a discussion on FB PowerShell group about overengineering your solutions. I’ve been guilty of that as well… Ralf wants to do everything in PowerShell and hey who can blame him? 😉 Ok Ralfie here goes…

First we’ll start with a mock CSV File with users:

#region: csvUsers
$csvUsers =
@"
UserID nieuw,LoginNaam Nieuw,Personeelsnummer,VoorNaam,AchterNaam,Afdeling,Bedrijf, PostCode,StraatNaam,Stad,LocationCode
150131,150131,150131,John,Wayne,IT,Acme,1222 XX,Elmstreet 26,Sciencefiction,1X
150141,150141,150141,Jane,Doe,HR,Acme,1222 XX,Elmstreet 26,Sciencefiction,2X
150211,150211,150211,Jack,Swift,IT,Acme,1222 XX,Elmstreet 26,Sciencefiction,3X
150211,150211,150211,James,Bond,CEO,Acme,0007 XX,Elmstreet 26,Sciencefiction,
"@ |
ConvertFrom-Csv -Delimiter ','
#endregion

Now I usually do my formatting in Excel but to entertain Ralfie, let’s do it in PowerShell, why? Because we can!!!

#region: csvLocation
$csvLocation =
@"
LocationCode,StraatNaam,PostCode,Stad
1X,Elmstreet 26,1666 XX,NightMare
2X,PeterPan 01,0001 ZT,Neverland
3x,Mulan 5, 1111 TK, Disney
"@ |
ConvertFrom-Csv -Delimiter ','

#Group as a hashtable by 'LocationCode'
$lookupLocationCode = $csvLocation | Group-Object -AsHashTable -Property 'LocationCode'
#endregion

Noticed the greyed line (no, not the scrollbar the other grey line)? That’s the key to changing location information. I don’t remember where I found this trick. I do know that Jeff Hicks recently post an article about group-object so I’m guessing it’s probably from Jeff.

And now we need to translate the CSV to something readable by a cmdlet. By the way, I noticed that tab delimiters could be an issue so I used comma this time, that way the example works…

#region: HashTable to translate csv Header
$hshHeader =@{
   'UserID nieuw' = 'SamAccountName'
   Personeelsnummer = 'EmployeeID'
   VoorNaam = 'GivenName'
   AchterNaam = 'SurName'
   Afdeling = 'Department'
   Bedrijf = 'Company'
   PostCode = 'PostalCode'
   StraatNaam = 'StreetAddress'
   Stad = 'City'
}

The hash table will help later on to convert the CSV Header into parameters a cmdlet understands. There’s an up and downside to this approach. The downside is that if the header changes in the csv File you need to update the changes here. no more blackbox effect. The upside, no manual editing. It’s a tradeoff, you decide if it’s worth it…

Here’s the main part. See if you can connect the dots… 😉

#region: Main

#Empty Array to save results
$arrResults = @()

foreach ($user in $csvUsers) {
   $UserProperties = @{}

   foreach ($property in $hshHeader.Keys) {
      $userValue = [string]$user.$property

      #Only add to the hashtable if it has a value otherwise splat will fail
      #You could make the value = $null as an alternative
      if (!([string]::isNullOrEmpty($userValue))) {
         $UserProperties += @{$($hshHeader[$property])=$userValue}
      }
   }

   #Add the hash Key Identity to the table. Identity key value equals SamAccountName
   #You can also remove from the hash table if necessary.
   $UserProperties.Add('Identity', $UserProperties.SamAccountName)

   #Change the current value to the one found in csvLocation
   if (!([string]::isNullOrEmpty($User.'LocationCode'))) {
      $UserProperties['City'] = $lookupLocationCode.$($User.'LocationCode').Stad
      $UserProperties['StreetAddress'] = $lookupLocationCode.$($User.'LocationCode').StraatNaam
      $UserProperties['PostalCode'] = $lookupLocationCode.$($User.'LocationCode').PostCode
   }
   else {
      $UserProperties['City'] = $null
      $UserProperties['StreetAddress'] = $null
      $UserProperties['PostalCode'] = $null
   }

   $arrResults += New-Object PSObject -Property $UserProperties
}
#endregion

And last but not least present new results in Out-GridView

#region: Present results in Out-GridView
$arrResults |
Select-Object EmployeeId,SamAccountName,GivenName,SurName,Department,Company,StreetAddress,PostalCode,City |
Out-GridView -Title "New CSV translated and updated with Location Code - $(Get-Date)"
#endregion

Noticed that James Bond doesn’t have a forwarding Address anymore (because he’s a secret agent and shouldn’t have been listed in the first place!). That’s the kind of fun you can have with PowerShell.

Here’s the full script

#region: csvUsers
$csvUsers =
@"
UserID nieuw,LoginNaam Nieuw,Personeelsnummer,VoorNaam,AchterNaam,Afdeling,Bedrijf, PostCode,StraatNaam,Stad,LocationCode
150131,150131,150131,John,Wayne,IT,Acme,1222 XX,Elmstreet 26,Sciencefiction,1X
150141,150141,150141,Jane,Doe,HR,Acme,1222 XX,Elmstreet 26,Sciencefiction,2X
150211,150211,150211,Jack,Swift,IT,Acme,1222 XX,Elmstreet 26,Sciencefiction,3X
150211,150211,150211,James,Bond,CEO,Acme,0007 XX,Elmstreet 26,Sciencefiction,
"@ |
ConvertFrom-Csv -Delimiter ','
#endregion

#region: csvLocation
$csvLocation = 
@"
LocationCode,StraatNaam,PostCode,Stad
1X,Elmstreet 26,1666 XX,NightMare
2X,PeterPan 01,0001 ZT,Neverland
3x,Mulan 5, 1111 TK, Disney
"@ |
ConvertFrom-Csv -Delimiter ','

#Group as a hashtable by 'LocationCode'
$lookupLocationCode = $csvLocation | Group-Object -AsHashTable -Property 'LocationCode'
#endregion

#region: HashTable to translate csv Header
$hshHeader =@{
   'UserID nieuw' = 'SamAccountName'
   Personeelsnummer = 'EmployeeID'
   VoorNaam = 'GivenName'
   AchterNaam = 'SurName'
   Afdeling = 'Department'
   Bedrijf = 'Company'
   PostCode = 'PostalCode'
   StraatNaam = 'StreetAddress'
   Stad = 'City'
}

#region: Main

#Empty Array to save results
$arrResults = @()

foreach ($user in $csvUsers) {
   $UserProperties = @{}

   foreach ($property in $hshHeader.Keys) {
      $userValue = [string]$user.$property

      #Only add to the hashtable if it has a value otherwise splat will fail
      #You could make the value = $null
      if (!([string]::isNullOrEmpty($userValue))) {
         $UserProperties += @{$($hshHeader[$property])=$userValue}
      }
   }

   #Add the hash Key Identity to the table. Identity key value equals SamAccountName
   #You can also remove from the hash table if necessary
   $UserProperties.Add('Identity', $UserProperties.SamAccountName)

   #Change the current value to the one found in csvLocation
   if (!([string]::isNullOrEmpty($User.'LocationCode'))) {
      $UserProperties['City'] = $lookupLocationCode.$($User.'LocationCode').Stad
      $UserProperties['StreetAddress'] = $lookupLocationCode.$($User.'LocationCode').StraatNaam
      $UserProperties['PostalCode'] = $lookupLocationCode.$($User.'LocationCode').PostCode
   }
   else {
      $UserProperties['City'] = $null
      $UserProperties['StreetAddress'] = $null
      $UserProperties['PostalCode'] = $null
   }

   $arrResults += New-Object PSObject -Property $UserProperties
}
#endregion

#region: Present results in Out-GridView
$arrResults | 
Select-Object EmployeeId,SamAccountName,GivenName,SurName,Department,Company,StreetAddress,PostalCode,City |
Out-GridView -Title "New CSV input updated with Location Code - $(Get-Date)"
#endregion

Well Ralfie there you have it, now it’s you turn to pay it forward…

Hope it’s worth something to you…

Ttyl,

Urv

Advertisements

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 {
   [PSCustomObject]@{
      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? 😉

Ttyl,

Urv