Rob_C Rob_C - 3 months ago 21
PowerShell Question

copy and alter column in CSV

I will daily be needing to modify a CSV so I can provision accounts. It requires simple information in and creating new accounts. I can handle the PowerShell for creating accounts in AD, it's modifying the CSV. I need to create a username and an email address with additional text.

Input will look like this:

First,Last,School,Attribute,Description
John,Smith,Elementary,A2F,teacher
Joe,Johnson,High School,A2F,teacher
Greg,Harris,Middle School,A2F,teacher


Output needs to look like this:

First,Last,School,Attribute,Description,Username,Email
John,Smith,Elementary,A2F,teacher,JSmith.sfo,John.Smith.sfo@googledomain.org
Joe,Johnson,High School,A2F,teacher,JJohnson.sfo,Joe.Johnson.sfo@googledomain.org
Greg,Harris,Middle School,A2F,teacher,GHarris.sfo,Greg.Harris.sfo@googledomain.org

Answer

Use Select-Object with a calculated property for each new column you want:

Import-Csv .\old.csv |
Select-Object *,@{Name='Username';Expression={'{0}{1}.sfo' -f $_.First[0],$_.Last}},@{Name='Email';Expression={'{0}.{1}.sfo@googledomain.org' -f $_.First,$_.Last}} |
Export-Csv .\new.csv -NoTypeInformation