powershell csv import error

jonathangeiselman · 2016-08-16T18:57:23.000Z

I have written this powershell script but i get an error when I run it. I’m sure the syntax is off on something but staring at it has yielded no eureka moments. I believe the issue is in the “if not null” statement but I’m not sure. The error is “Get-ADuser : The search filter cannot be recognized” if(Get-ADUser -filter $filter) The CSV’s fields are pretty obvious except maybe GR = grade level

#begin fie processing
Import-Module activedirectory

#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv "C:\sftp\Student.csv"

#Set Current School Year Variable
$currentschoolyr = 2016

#Loop through each row containing user details in the CSV file 
foreach ($User in $ADUsers)
{
    if ($User.StateIDNumber -ne $null -or $User.GR -ne $null)
    {
    #Read user data from each field in each row and assign the data to a variable as below

    $stFirstorg  = $User.FName
    $stLastorg   = $User.LName
    $stMiddleorg = $User.MName
    $stoffice     = $User.GR
    $stPassword   = $User.StateIDNumber
	
	#Calculate Graduation Year
	if ($User.GR -match '^\d+$')
		{
        $stGrad = 13-$User.GR+$currentschoolyr
		}
	else
		{
		$stGrad = $currentschoolyr + 13
		}
		
	#Manipulate Text length and contents	
    [string]$stGraduation = $stGrad
    $stFirstname = $stFirstorg.ToLower()
    $stLastname = $stLastorg.ToLower() 
    $stMiddlename = $stMiddleorg.ToLower()
    $stmidname = $stMiddlename.substring(0,[System.Math]::Min(6, $stMiddlename.Length))
    $stfirstint = $stFirstname.substring(0,1)

    if (-not $stMiddleorg)
        {
        $stMidint = $null
        $dispname =$stFirstname + " " + $stLastname
        }
    else
        {
        $stMidint = $stMiddlename.substring(0,1)
        $dispname =$stFirstname + " " + $stMiddlename + " " + $stLastname
        }

    $stlastman1 = $stLastname.replace(' ','')
	$stlastman2 = $stlastman1.replace('-','')
	$stlastclean = $stlastman2
    $stgradyr =$stGraduation.substring(2,2)
	
    $userFML=$stfirstint+$stMidint+$stlastclean.substring(0,[System.Math]::Min(16, $stlastclean.Length))
    $stUser=$userFML+$stgradyr

	#Check it user account exists
    if (Get-ADUser -F {SamAccountName -eq $stUser})
    {  
        # Write-Warning "User account exists, add second char to first name"
        $stfirst2 = $stFirstname.substring(0,2)
        $stUser2=$stfirst2+$stMidint+$stlastclean.substring(0,[System.Math]::Min(16, $stlastclean.Length))
        $stUsername = $stUser2
    }
    else
    {
        #Username $stUser is Good!"
        $stUsername = $stUser
    }
    
    $OU = "OU=imported,DC=mydomain,DC=local"
    $stdisplayname = $dispname
    $sttitle = $User.StateIDNumber
    $stdepartment = "Student"
    $Domain="@ADDomain.local"
    $emaildomain="@Myemaildomain.com"
    $UPN=$stUsername+$Domain
    $STEmail=$stUsername+$emaildomain

    #Check to see if the user already exists in AD
    $filter = "Title -eq '$($sttitle)'"
    #Write-Host "Filter is $filter"
    if (Get-ADUser -filter $filter)
    {
        #If user does exist, give a warning
        #Write-Warning "A user account with name $stdisplayname already exists in Active Directory."
        #Write-hostName $stdisplayname 
        #Write-hostSamAccountName $stUsername 
        #Write-hostUserPrincipalName $UPN  
        #Write-hostGivenName $stFirstname 
        #Write-hostSurname $stLastname 
        #Write-hostInitials $stmidname  
        #Write-hostDepartment $stdepartment 
        #Write-hostDisplayName $stdisplayname 
        #Write-hostDescription $stGraduation 
        #Write-hosttitle $stPassword 
        #Write-hostoffice $stoffice 
        #Write-hostEmailAddress $STEmail   
    }
    else
    {
        Write-Warning "Creating account for user $stdisplayname."
        #Write-host
        #Write-host Name $stdisplayname 
        #Write-host SamAccountName $stUsername 
        #Write-host UserPrincipalName $UPN  
        #Write-host GivenName $stFirstname 
        #Write-host Surname $stLastname 
        #Write-host Initials $stmidname  
        #Write-host Department $stdepartment 
        #Write-host DisplayName $stdisplayname 
        #Write-host Description $stGraduation 
        #Write-host title $stPassword 
        #Write-host office $stoffice 
        #Write-host EmailAddress $STEmail
          
        #User does not exist then proceed to create the new user account
        #Account will be created in the OU provided by the $OU variable read from the CSV file
        #New-ADUser -Name $stdisplayname -SamAccountName $stUsername -UserPrincipalName $UPN  -GivenName $stFirstname -Surname $stLastname -Initials $stmidname  -Department $stdepartment -DisplayName $stdisplayname -Description $stGraduation -title $stPassword -office $stoffice -EmailAddress $STEmail -Path $OU -Enable $True -AccountPassword (convertto-securestring $stPassword -AsPlainText -Force) 
    }
    }
    else
    {
    Write-Warning "$User.LName ------- Missing INFO!!!!"
    }
    
   

} #end function

Thanks in advanced

gungnir · 2016-08-16T19:28:38.000Z

Seems like that part of the code should work, from my tests. Give it a try like this and see if it makes a difference.

$Filter = { Title -eq $STTitle }

if( Get-ADUser -Filter $Filter ){
    # Code here
    }

Or, instead of the extra variable, try putting it all in a single line.

if( Get-ADUser -Filter { Title -eq $STTitle } ){
    # Code here
    }

DoctorDNS · 2016-08-16T19:53:00.000Z

Consider stripping down the script to a much fewer lines. It is not easy to debug such long scripts. Doing so may help you see the issue!

jitensh · 2016-08-16T21:40:10.000Z

try {
    if (!(get-aduser -Filter {samaccountname -eq "$stUser"})){
        New-ADUser blah blah
    else {
        Write-Warning "[WARNING] Samaccount for username [$($stUser)] already exists"
      
    }

try with sam

jonathangeiselman · 2016-08-17T13:32:54.000Z

Sorry about the length, I’ve narrowed it down to if the last column is empty ex: data,data,data, it errors out. I’ll test the suggestions tomorrow. Thanks

jonathangeiselman · 2016-08-17T14:07:11.000Z

Gungnir,

Making the change to the -filter statement yielded the same result:
Get-ADUser : The search filter cannot be recognized
At C:\sftp\INow Student Import.ps1:86 char:9

if (Get-ADUser -filter { Title -eq $sttitle } )
CategoryInfo : NotSpecified: ( [Get-ADUser], ADException
FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Comman
ds.GetADUser

tfl:

I thinned down the script to:

#begin fie processing
Import-Module activedirectory

#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv "C:\sftp\test.csv"

#Loop through each row containing user details in the CSV file 
foreach ($User in $ADUsers)
{
    if ($User.StateIDNumber -ne $null -or $User.GR -ne $null)
    {
    $stUser = $User.sam
    $sttitle = $User.StateIDNumber
	    #Check it user account exists
        if (Get-ADUser -F {SamAccountName -eq $stUser})
        {  
        Write-Warning "$stUser found"
        }
        else
        {
        Write-Warning "$stUser not found"
        }
    
        #Check to see if the user already exists in AD
        if (Get-ADUser -filter { Title -eq $sttitle } )
        {
        Write-Warning "$sttitle found"
        }
        else
        {
        Write-Warning "$sttitle not found"
        }
    }
    else
    {
    Write-Warning "$User.LName ------- Missing INFO!!!!"
    }
    
   

} #end function

The csv file is now (sam,GR,StateIDNumber) and if the last column is empty i get the error:
Get-ADUser : The search filter cannot be recognized
At C:\sftp\logic test.ps1:25 char:13

if (Get-ADUser -filter { Title -eq $sttitle } )
CategoryInfo : NotSpecified: ( [Get-ADUser], ADException
FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Comman
ds.GetADUser

So the line: if ($User.StateIDNumber -ne $null -or $User.GR -ne $null) is not working. its not seeing the column as being null/empty. if there is something in the column then it run great. Thanks again in advanced

gungnir · 2016-08-17T15:00:10.000Z

jonathangeiselman:

The csv file is now (sam,GR,StateIDNumber) and if the last column is empty i get the error:
Get-ADUser : The search filter cannot be recognized
At C:\sftp\logic test.ps1:25 char:13
if (Get-ADUser -filter { Title -eq $sttitle } )
CategoryInfo : NotSpecified: ( [Get-ADUser], ADException

FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Comman
ds.GetADUser
So the line: if ($User.StateIDNumber -ne $null -or $User.GR -ne $null) is not working. its not seeing the column as being null/empty. if there is something in the column then it run great. Thanks again in advanced

That’s because you’re using an -or statement - this expression will evaluate to true if either of the fields is not $null. Do you need to check if the grade level is set here? If not then you can change the expression to

if( $User.StateIDNumber ){
    # Code here
    }

If you do have to check that field here, then the -and operator would be more appropriate.

if( $User.StateIDNumber -and $User.GR ){
    # Code here
    }

jonathangeiselman · 2016-08-17T15:53:59.000Z

Thanks Gungnir! I was over complicating it, that did the trick.