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
pshirwin 