‘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:
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
pshirwin 