Reset User Passwords and Add Missing Users to Active Directory via PowerShell from Google Accounts

MySQL SQL Code

SELECT
     SUBSTRING_INDEX(Google.primaryEmail , '@' , 1) AS 'SamAccountName' ,
     Google.name.givenName AS 'GivenName' ,
     Google.name.familyName AS 'Surname' ,
     Google.name.fullName AS 'DisplayName' ,
     CASE CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(Google.orgUnitPath , '/' , - 1), ' ', -1),UNSIGNED INTEGER)
         WHEN 6 THEN "OU=7,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
         WHEN 7 THEN "OU=8,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
         WHEN 8 THEN "OU=9,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
         WHEN 9 THEN "OU=10,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
         WHEN 10 THEN "OU=11,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
         WHEN 11 THEN "OU=12,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
     END AS 'Path' ,
     "" AS 'AccountPassword' ,
     Google.primaryEmail AS 'EmailAddress' ,
     CASE Google.externalIds.0.value 
         WHEN "" THEN UPPER(SUBSTRING_INDEX(Google.primaryEmail , '@' , 1))
         ELSE Google.externalIds.0.value
     END AS 'EmployeeID'
 FROM
     Google
 WHERE
     (
         Google.orgUnitPath = '/Students/Upper Primary/Year 6'
         OR Google.orgUnitPath LIKE '/Students/Middle%'
         OR Google.orgUnitPath LIKE '/Students/Senior%'
     )
     AND 
         Google.orgUnitPath != '/Students/Senior School/Year 12' AND Google.primaryEmail NOT LIKE '%test%' 
 ORDER BY 
     CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(Google.orgUnitPath , '/' , - 1), ' ', -1),UNSIGNED INTEGER) ASC,
     Google.name.familyName ASC,
     Google.name.givenName ASC

Generating Passwords with pwgen

$ pwgen -1 -c -n -y -B 12 10

 gie9eiC]oh)W
 ohCho/e4xeer
 ixohY9Ag@aim
 Aes3zeeph,ie
 ooH9eingah*k
 che!id3uy<ie
 Phoo!Kee7iem
 mai4ze#u7The
 Phah(b7adoe;
 eu4osh4Fee)b

PowerShell Script

$Import =Import-CSV "c:\Test\import.csv"
ForEach ($user in $Import)
 {
     $Error.Clear()
     try
     {
         Set-ADAccountPassword –Identity $user.SamAccountName –Reset –NewPassword (ConvertTo-SecureString -AsPlainText $user.AccountPassword -Force)    
         Write-Output "Password for $($user.SamAccountName) changed to $($user.AccountPassword)."
     }
     catch
     {
         $password = $user.AccountPassword | ConvertTo-SecureString -AsPlainText -Force
         New-ADUser -Name $user.SamAccountName -SamAccountName $user.SamAccountName -GivenName $user.GivenName -Surname $user.Surname -DisplayName $user.DisplayName -EmailAddress $user.EmailAddress -EmployeeID $user.EmployeeID -Path $user.Path -AccountPassword $Password -ChangePasswordAtLogon $True -Enabled $True 
         Write-Output "Created account for $($user.SamAccountName) and set the password to $($user.AccountPassword)."
     }
}