Help Simplifying Code

tim-blough · June 5, 2014, 1:30am

I’m new to scripting and am attempting to create a sript that will process data from a csv file into variables that I can use to automatically create users in Active Directory. The file contains a grade level that must be converted to graduation year to be used in proper nameing of the user account and the proper placement of the the user account into the OUs in Active Directory.

Currently, the code written will read the csv file 4 times (12 times if completed this way). This seems like a lot of overhead. Being able to reference the grade level to graduating class in the lines would really be helpful but the order of syntax is eluding me.

Graduation years will remain the same for users but the grade changes every year. I don’t want to have to change 24+ lines of code every school year (that’s just asking for a problem). I thought I could reference a hash table inline to make appropriate changes and also limit the number of reads from the csv file.

######################################
## Create, Disable Student Accounts ##
##   from file imported from DASL   ##
######################################

# Change Current School Year
$currentYear = 2014


##Build grade_code to ClassOf table
$Classof =@{
    "12" = $currentYear++
    "11" = $currentYear++
    "10" = $currentYear++
    "9" = $currentYear++
    "8" = $currentYear++
    "7" = $currentYear++
    "6" = $currentYear++
    "5" = $currentYear++
    "4" = $currentYear++
    "3" = $currentYear++
    }

##Import Student Data, Removing apostrophies and hypens from certain fields. 
##Description changed based on graduation Year
$AllStudents = import-csv C:\StuGroups\Students.csv
$2014Students = import-csv C:\StuGroups\Students.csv | Where-Object {$_.grade_code -eq "12"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2014 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}},
        @{n='Title';e={"Student"}}
$2015Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "11"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2015 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}},
        @{n='Title';e={"Student"}}
$2016Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "10"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2016 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}},
        @{n='Title';e={"Student"}}
$2017Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "9"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2017 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("backtick'","") + "." + $_.first_name.replace("-","").replace("backtick'","")}},
        @{n='Title';e={"Student"}}

##  AND 8 MORE GRADES AFTER THE LAST ONE ABOVE

$2014Students | Export-Csv C:\StuGroups\2014Students.csv -NoTypeInformation
$2015Students | Export-Csv C:\StuGroups\2015Students.csv -NoTypeInformation
$Classof
#Write-Output $2016Students.count
#Write-Output $2017Students.count

system · June 5, 2014, 1:38am

That code is formatted very oddly in the forums. It might help if you save a copy of it with a .txt extension and attach it instead. (Don’t try to attach it as .ps1; it’ll get blocked.)

Also, a quick plain-English explanation of what the code is supposed to do will be helpful. Examples of the input and output CSV formats could also help, without having to read through all of those constructed properties and .Replace() stuff.

tim-blough · June 5, 2014, 1:43am

I was fixing the back ticks as you were reading the post.

This is just the start of the code that will import student information from a csv file, clean up the data, create new fields for user creation in Active Directory.

The csv files I have are filled with a ton of personal information. I’ll do my best to create a file filled with generic info.

tim-blough · June 5, 2014, 2:02am

attaceded students. file.

system · June 5, 2014, 2:26am

Below I’ve pasted what I think your original code was supposed to look like. Please look it over (via the website; in an email, you may see all sorts of HTML escape sequences), and let me know if it looks correct.

From what I can see here, you have a “grade_code” which associated with a year. 2014 = 12, 2015 = 11, etc. Does this association of codes change from year to year? What happens when you get down to 0 and negative numbers for 2026 / 2027?

You’re splitting the source CSV file into multiple new CSV files by year, with some transformation of the data happening as well.

It should be pretty straightforward to make this more generic and clean, once I understand how the $classof table is going to look each year. Right now, you have this:

12 = 2014
11 = 2015
10 = 2016
9 = 2017
…etc

What will this table look like in 2015, 2016, and so on? Once I understand that, I can suggest some changes.

######################################
## Create, Disable Student Accounts ##
##   from file imported from DASL   ##
######################################

# Change Current School Year
$currentYear = 2014


##Build grade_code to ClassOf table
$Classof =@{
    "12" = $currentYear++
    "11" = $currentYear++
    "10" = $currentYear++
    "9" = $currentYear++
    "8" = $currentYear++
    "7" = $currentYear++
    "6" = $currentYear++
    "5" = $currentYear++
    "4" = $currentYear++
    "3" = $currentYear++
    }

##Import Student Data, Removing apostrophies and hypens from certain fields. 
##Description changed based on graduation Year
$AllStudents = import-csv C:\StuGroups\Students.csv
$2014Students = import-csv C:\StuGroups\Students.csv | Where-Object {$_.grade_code -eq "12"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2014 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}},
        @{n='Title';e={"Student"}}
$2015Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "11"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2015 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}},
        @{n='Title';e={"Student"}}
$2016Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "10"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2016 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}},
        @{n='Title';e={"Student"}}
$2017Students = import-csv C:\StuGroups\Students.csv |Where-Object {$_.grade_code -eq "9"} |
        Select student_code,last_name,first_name,grade_code, 
        @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
        @{n='loginName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}}, 
        @{n='email';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","") + "@school.org"}}, 
        @{n='description';e={"Class of 2017 Student ID :" + $_.student_code}},
        @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("`'","") + "." + $_.first_name.replace("-","").replace("`'","")}},
        @{n='Title';e={"Student"}}

##  AND 8 MORE GRADES AFTER THE LAST ONE ABOVE

$2014Students | Export-Csv C:\StuGroups\2014Students.csv -NoTypeInformation
$2015Students | Export-Csv C:\StuGroups\2015Students.csv -NoTypeInformation
$Classof
#Write-Output $2016Students.count
#Write-Output $2017Students.count

tim-blough · June 5, 2014, 3:04am

In 2015, I’ll change the current year to 2015 and then:
12 = 2015
11 = 2016
10 = 2017
9 = 2018
etc.

I just cant figure out how to call the array and return results while the files is being piped through. Will I need to drop pipes and use a foreach?

tim-blough · June 5, 2014, 3:29am

A little more clarification I should have added. This script is for a U.S. k-12 School.

Our Active Directory Structure is built upon students being placed in OUs named by their graduation years, that way we need only move one OU from building to building as the student progresses through our district. When the seniors graduate, we simply delete the OU for the school year that just ended. Likewise, we create a new OU each year for the new kindergarten class (well, actually we start their AD accounts at grade 3).

Once I learn how to reference the $Classof table in the lines of script importing the students, I can remove the redundancy of creating a csv file for each graduation class, Cutting the number of import-csv’s down to one and reducing the overall script length down tremendously.

system · June 5, 2014, 3:56am

I see. Try this, as a starting point. It cuts way down on duplicate code by doing lookups into your $classof table, and using Group-Object to generate the separate CSV files by year at the end:

######################################
## Create, Disable Student Accounts ##
##   from file imported from DASL   ##
######################################

# Change Current School Year
$currentYear = 2014


##Build grade_code to ClassOf table
$Classof = @{
    "12" = $currentYear++
    "11" = $currentYear++
    "10" = $currentYear++
    "9" = $currentYear++
    "8" = $currentYear++
    "7" = $currentYear++
    "6" = $currentYear++
    "5" = $currentYear++
    "4" = $currentYear++
    "3" = $currentYear++
}

##Import Student Data, Removing apostrophies and hypens from certain fields. 
##Description changed based on graduation Year
$AllStudents = import-csv C:\StuGroups\Students.csv |
                Select student_code,last_name,first_name,grade_code, 
                @{n='DisplayName';e={$_.last_name  + ", " + $_.first_name}},
                @{n='loginName';e={$_.last_name.replace("-","").replace("'","") + "." + $_.first_name.replace("-","").replace("'","")}}, 
                @{n='email';e={$_.last_name.replace("-","").replace("'","") + "." + $_.first_name.replace("-","").replace("'","") + "@school.org"}}, 
                @{n='description';e={"Class of $($Classof[$_.grade_code]) Student ID :$($_.student_code)"}},
                @{n='sAMAccountName';e={$_.last_name.replace("-","").replace("'","") + "." + $_.first_name.replace("-","").replace("'","")}},
                @{n='Title';e={"Student"}}


$AllStudents |
Group-Object -Property grade_code |
ForEach-Object {
    $groupInfo = $_
    $year = $Classof[$groupInfo.Name]
    $groupInfo.Group | Export-Csv -Path "C:\StuGroups\${year}Students.csv" -NoTypeInformation
}

daniel-krebs · June 5, 2014, 4:01am

Tim,

You’ll definitely need to use a loop construct like foreach to simplify your code. Please check out below example for ideas.


$Year = 2014
$AllStudents = Import-Csv -Path C:\StuGroups\Students.csv
foreach ($grade in (12..3)) 
{
  $AllStudents | Where-Object {$_.grade_code -eq $grade} | Select-Object ....
  $Year++ 
}

tim-blough · June 5, 2014, 5:10am

Wow.

You guys are a great help.
I have it working using Dave’s suggestion. $($Classof[$_.grade_code]) was the syntax I was looking for. Why do I need the $ before the parenthesis? I was tying curly brackets I wish I could attend a course on this.

Daniel, I’d like to try yours as well but I’m definitely needing to build on my Power Shell knowledge.
I see the logic in calling the loop and running through on each grade, but how to I append the results into one file or object?

Thanks for you help. Now onto the actual creation of Active Directory objects. This stuff is fun.

system · June 5, 2014, 5:16am

$() is the subexpression operator. It’s most commonly used when you want to embed an expression into a double-quoted string (as opposed to just expanding a simple variable within the string). You can see this in action (and the need for it) by running the following example code:

$myVariable = 5

"My Variable = $myVariable"
"My Variable + 5 = $myVariable + 5"
"My Variable + 5 = $($myVariable + 5)"

Expressions can be basically any valid PowerShell command. Math like that example, indexing into an array or hashtable, accessing a property of a variable such as $($myVariable.SomeProperty), calling a cmdlet, you name it. For readability, try to keep them short; when in doubt, run the expression beforehand and assign the result to a variable, then use the variable inside the expanded string. This isn’t really an option when using Select-Object the way your code does, but since the expression is short and readable, it doesn’t really matter.

If you prefer, you could also have used the + operator for string concatenation:

@{n='description';e={"Class of " + $Classof[$_.grade_code] + " Student ID :" + $_.student_code}},

That’s a habit I try to avoid, since string concatenation can be very slow, for large values. For really short strings like this, though, you’d never notice the difference.

mjolinor · June 5, 2014, 6:06am

That string construction seems a little tortured. I’d suggest;

@{n=‘loginName’;e={(‘{0}.{1}’ -f $.last_name,$.first_name).replace(“-'”,“”)}}, @{n=‘email’;e={(‘{0}.{1}@school.org’ -f $.last_name,$.first_name).replace(“-'”,“”)}}

tim-blough · June 5, 2014, 6:53am

Rob,
I’ll take the suggestion. I just need to wrap my head around what is happening there.
I’m guessing that {0}.{1} is placeholders for the values being assigned later? what is -f doing?

daniel-krebs · June 5, 2014, 7:10am

Tim,

-f is the PowerShell Format operator. Which is quite nice way to build strings with dynamic values.

From about_Operators (http://technet.microsoft.com/en-us/library/hh847732.aspx)

-f Format operator
Formats strings by using the format method of string
objects. Enter the format string on the left side of the operator
and the objects to be formatted on the right side of the operator.

C:\PS> “{0} {1,-10} {2:N}” -f 1,“hello”,[math]::pi
1 hello 3.14

For more information, see the String.Format method
(http://go.microsoft.com/fwlink/?LinkID=166450) and
Composite Formatting (http://go.microsoft.com/fwlink/?LinkID=166451).

martin-nielsen · June 5, 2014, 8:00am

I took the liberty of doing away with the middle part and making it look a bit more pretty.


$AllStudentsCsv = Import-Csv C:\StuGroups\Students.csv
$AllStudents = @()
foreach($student in $AllStudentsCsv) {
    # Let's not waste CPU cycles and screen space doing the same thing over and over
    $firstNameFiltered = $student.first_name.Replace("-","").Replace("'","")
    $lastNameFiltered = $student.last_name.Replace("-","").Replace("'","")
    # More string formatting, less piecing bits of string together
    $AllStudents += New-Object PSObject -Property @{
        'DisplayName' = "{0}, {1}" -f $student.last_name, $student.first_name
        'loginName' = "{0}.{1}" -f $lastNameFiltered, $firstNameFiltered
        'email' = "{0}.{1}@school.org" -f $lastNameFiltered, $firstNameFiltered
        'description' = "Class of {0} Student ID: {1}" -f $Classof[$student.grade_code], $student.student_code
        'sAMAccountName' = "{0}.{1}" -f $lastNameFiltered, $firstNameFiltered
        'Title' = 'Student'
    }
}

mjolinor · June 5, 2014, 8:15am

That array addition is probably costing you more cpu than you saved by eliminating the string replace method calls.

If you’re going to do that, do it this way:


$AllStudents = 
foreach($student in $AllStudentsCsv) {
    # Let's not waste CPU cycles and screen space doing the same thing over and over
    $firstNameFiltered = $student.first_name.Replace("-","").Replace("'","")
    $lastNameFiltered = $student.last_name.Replace("-","").Replace("'","")
    # More string formatting, less piecing bits of string together
    New-Object PSObject -Property @{
        'DisplayName' = "{0}, {1}" -f $student.last_name, $student.first_name
        'loginName' = "{0}.{1}" -f $lastNameFiltered, $firstNameFiltered
        'email' = "{0}.{1}@school.org" -f $lastNameFiltered, $firstNameFiltered
        'description' = "Class of {0} Student ID: {1}" -f $Classof[$student.grade_code], $student.student_code
        'sAMAccountName' = "{0}.{1}" -f $lastNameFiltered, $firstNameFiltered
        'Title' = 'Student'
    }
}

martin-nielsen · June 5, 2014, 5:57pm

I didn’t quite believe you so I ran 1000 iterations of 10 sample users to see for myself.

Turns out my method is faster by 400ms than the string replace method, but your way beats mine by 600ms.

I wasn’t aware that $variable = foreach() {} was even valid. If I had given it more thought I might have ended up putting the foreach inside of a scriptblock, but that’s probably the best I would’ve come up with.

mjolinor · June 5, 2014, 10:45pm

See this:

Array and string concatenations are relatively “expensive” operations, and should be avoided inside a loop. I see very few instances where it’s being done that can’t be implemented by simply using the pipeline. In cases where the pipeline is already being used and the accumulation is incidental to the pipeline output, using a stringbuilder for string concatenations, and arraylists instead of arrays are usually much better solutions from a performance standpoint.

tim-blough · June 5, 2014, 11:07pm

You guys rock!

tim-blough · June 6, 2014, 12:42am

O.k. So if I wanted to filter out Kindergarten through Grade 2. I would think I could do this:
$AllStudentsCsv = Import-Csv C:\StuGroups\Students.csv | Where-Object {$.grade_code -ne “KG” -or $.grade_code -ne “1” -or $_.grade_code -ne “2”}

They still show up.

Topic		Replies	Views
Powershell script to pull data from AD and make a table PowerShell Help	26	348	May 16, 2024
Please help on speed up my code PowerShell Help	15	473	May 16, 2024
Active Directory - Bulk - DisplayName Update -Formatting PowerShell Help	17	3695	May 16, 2024
Reading from Excel and Exporting to CSV? PowerShell Help	1	401	May 16, 2024
Change script from Excel object to CSV PowerShell Help	1	189	May 16, 2024

Help Simplifying Code

Related topics