Share via

PowerShell v3 - Get Variables from Table Columns

  • Article

Question

Monday, November 4, 2013 2:51 AM

Hey all,

In PowerShell v2 I was able to loop through columns and rows of a Data Table using the following code:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=$MySQLServer;database=$DatabaseName;uid=$Username;trusted_connection=true;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ("ADI_StaffImport","ADMIN")
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$data = $dataset.Tables[0]

foreach ($data_item[0] in $data.Rows)
{   
    $UserExist = (!([ADSI]::Exists("LDAP://CN=$($data_item[0]),$($StaffADContainer)")))
    if ($UserExist -eq "False")
    {
        $samAccountName = $data_item[0]
        $Surname = $data_item[1]
        $FirstName = $data_item[2]      
    }
}

The purpose of this script is to get user information from an SQL query and automatically create, edit and remove users from Active Directory. My issue is that when I run this script using PowerShell v3, I get the following error:

At line:1 char:15

  • foreach ($data_item[0] in $data.Rows)
    +                        ~
    Missing 'in' after variable in foreach loop.
    At line:1 char:37
  • foreach ($data_item[0] in $data.Rows)
    +                                                             ~
    Unexpected token ')' in expression or statement.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : MissingInInForeach

I am aware there are syntax changes in PowerShell v3 however I have been unable to stop this error from occurring. Effectively, I cannot get variables from columns within the Data Table.

Anyone's help would be greatly appreciated.

All replies (6)

Tuesday, November 5, 2013 10:34 PM ✅Answered

Similar to what you have suggested, the below code returns a value:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=$($DatabaseServerName);database=$($DatabaseName);uid=$($DatabaseUsername);trusted_connection=true;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ("ADI_StaffImport","ADMIN")
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$data = $dataset.Tables[0]

foreach ($dataitem in $data.Rows)
{   
    if ([ADSI]::Exists('LDAP://CN={0},{1}' -f $dataitem[0], $StaffADContainer))
    {
      $samAccountName = $dataitem[0]
     $Surname = $dataitem[1]
        $FirstName = $dataitem[2]       
   }
}

Using the column name returned a null value, so the column number must be used, and without the underscore.

Thanks for getting me on the right track.

Monday, November 4, 2013 5:29 AM

A few things:

  1. I don't know how you got it to work in PS2 but the foreach line is wrong as far as I know.

2) The $UserExist -eq "False" does not work the way as you may think.

3) It is generally a bad idea to access data set objects as an array item, i.e. using index in collection to access the object.  Your code is at the mercy of consistency of the database schema and will break if it ever changes.  Simply speaking, how can you be so sure Tables[0] is the one you're after, unless it is the only table in the dataset??

  1. From your code you're assuming the samAccountName is identical to the Common Name (CN), which may not be always true.

This is how I'd do it (based on your model):

$data = $dataset.Tables['MyTableName']

foreach ($dateRow in $data.Rows)
{   
    if ([ADSI]::Exists('LDAP://CN={0},{1}' -f $dateRow['FirstColumnName'], $StaffADContainer))
    {
        $samAccountName = $dateRow['FirstColumnName']
        $Surname = $dateRow['SecondColumnName']
        $FirstName = $dateRow['ThirdColumnName']        
    }
}

Monday, November 4, 2013 5:55 AM

My issue is in relation to a syntax change between PowerShell v2 and v3. The code I have posted is only a small snippet to a much larger script, which works perfectly as it is intended.

There is only one Table, each column containing user information. The format of this table will never change - an SQL Query builds the table. Column 0 will always be the samAccountName, etc.

A larger example of my code is below, not including variables:

#MySQL databse connection commands
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=$($DatabaseServerName);database=$($DatabaseName);uid=$($DatabaseUsername);trusted_connection=true;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ("ADI_StaffImport","ADMIN")
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$data = $dataset.Tables[0]

#Test successful database connection. Script will terminate if a connection error occures.
if ($data -eq $null)
{
    exit
}

#Start AD Powershell Snap-in
Import-Module ActiveDirectory
#Start Exchange Powershell Snap-in
add-pssnapin Microsoft.Exchange.Management.PowerShell.E2010

#Run the Staff add loop
foreach ($data_item[0] in $data.Rows)
{   
    $ADICount = 0
    #Check if user is in the AD container Staff
    $UserExist = (!([ADSI]::Exists("LDAP://CN=$($data_item[0]),$($StaffADContainer)")))
    if ($UserExist -eq "False")
    {
        #Check if user is in ADI-Ignore
        $ADIGroup = Get-ADGroupMember -identity $ADIIgnoreGroup
        foreach($ADIUser in $ADIGroup)
        {
            if ($ADIUser.samAccountName -like "$($data_item[0])" -eq "true")
            {
                $ADICount = 1
            }
        }   
        if ($ADICount -eq 0)
        {
            #Check if user is in ADISandpit
            $SandpitUserExist = (!([ADSI]::Exists("LDAP://CN=$($data_item[0]),$($ADISandpitContainer)")))
            if ($SandpitUserExist -ne "False")
            {
                #Moves an existing user from ADISandpit into the Staff container, and moves all owned folders into the correct directories
                Move-ADObject -identity "CN=$($data_item[0]),$($ADISandpitContainer)" -TargetPath "$($StaffADContainer)"                    
                if (test-path -path "$($ZZZZStaffHomeFolder)\$($data_item[0])")
                {
                    Move-Item -path "$($ZZZZStaffHomeFolder)\$($data_item[0])" -destination "$($StaffHomeFolder)" -confirm:$false
                }
                if (test-path -path "$($ZZZZStaffIntranetFolder)\$($data_item[0])")
                {
                    Move-Item -path "$($ZZZZStaffIntranetFolder)\$($data_item[0])" -destination "$($StaffIntranetFolder)" -confirm:$false
                }
                if (test-path -path "$($ZZZZStaffStaffnetFolder)\$($data_item[0])")
                {
                    Move-Item -path "$($ZZZZStaffStaffnetFolder)\$($data_item[0])" -destination "$($StaffStaffnetFolder)" -confirm:$false
                }
                if (test-path -path "$($ZZZZStaffSubmitFolder)\$($data_item[0])")
                {
                    Move-Item -path "$($ZZZZStaffSubmitFolder)\$($data_item[0])" -destination "$($StaffSubmitFolder)" -confirm:$false
                }
                if (test-path -path "$($ZZZZStaffReportsFolder)\$($data_item[0])")
                {
                    Move-Item -path "$($ZZZZStaffReportsFolder)\$($data_item[0])" -destination "$($StaffReportsFolder)" -confirm:$false
                }
                
                #Add User to security groups
                $UserToGroup = Get-ADUser $data_item[0]
                Add-ADGroupMember -identity Staff -members $UserToGroup -server server.domain.com.au
                Add-ADGroupMember -identity Staff-NonADMIN -members $UserToGroup -server server.domain.com.au
                Add-ADGroupMember -identity Apple-Staff -members $UserToGroup -server server.domain.com.au
                clear-variable UserToGroup
                
                #Resets user password
                Set-ADAccountPassword -Identity "CN=$($data_item[0]),$($StaffADContainer)" -NewPassword (ConvertTo-SecureString "$($data_item[10])" -AsPlainText -force) -reset
                Set-ADUser -Identity "CN=$($data_item[0]),$($StaffADContainer)" -ChangePasswordAtLogon 1
            }
            elseif ($SandpitUserExist -eq "False")
            {
                $UserAlreadyExist = Get-ADUser -identity "$($data_item[0])"
                if ($UserAlreadyExist.samAccountName -eq "$($data_item[0])")
                {
                    Add-Content $OutputTextFile "$(Get-Date -format T) - !!ERROR!! User $($data_item[0]) already exists in Active Directory - no user created !!ERROR!!"
                }
                elseif ($UserAlreadyExist.samAccountName -ne "$($data_item[0])")
                {
                    #Create the new user, add user to security groups and create all directories
                    New-ADUser -sAMAccountname $data_item[0] -Name $data_item[0] -Surname $data_item[1] -GivenName $data_item[2] -Description $data_item[3] -HomeDrive $data_item[4] -HomeDirectory $data_item[5] -EmailAddress $data_item[6] -UserPrincipalName $data_item[7] -ScriptPath $data_item[9] -AccountPassword (ConvertTo-SecureString "$($data_item[10])" -AsPlainText -force) -DisplayName $data_item[11] -Path $StaffADContainer -ChangePasswordAtLogon 1 -Enabled 1
                    
                    #Create staff Home folder and set permissions
                    New-Item "$($StaffHomeFolder)\$($data_item[0])" -type directory
                        $HomeRights = [System.Security.AccessControl.FileSystemRights]"$($StaffHomePerm)"
                        $HomeUser = New-Object System.Security.Principal.NTAccount("domain\$($data_item[0])")
                        $HomeType =[System.Security.AccessControl.AccessControlType]::Allow
                        $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"
                        $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None 
                        $PermissionRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($HomeUser,$HomeRights,$InheritanceFlag,$PropagationFlag,$HomeType)
                        $HomeACL = Get-Acl "$($StaffHomeFolder)\$($data_item[0])"
                        $HomeACL.SetAccessRule($PermissionRule)
                        Set-Acl "$($StaffHomeFolder)\$($data_item[0])" $HomeACL
                    
                    #Create staff Intranet folder and set permissions
                    New-Item "$($StaffIntranetFolder)\$($data_item[0])" -type directory
                        $IntranetRights = [System.Security.AccessControl.FileSystemRights]"$($StaffIntranetPerm)"
                        $IntranetUser = New-Object System.Security.Principal.NTAccount("domain\$($data_item[0])")
                        $IntranetType =[System.Security.AccessControl.AccessControlType]::Allow
                        $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit" 
                        $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None 
                        $PermissionRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($IntranetUser,$IntranetRights,$InheritanceFlag,$PropagationFlag,$IntranetType)
                        $IntranetACL = Get-Acl "$($StaffIntranetFolder)\$($data_item[0])"
                        $IntranetACL.SetAccessRule($PermissionRule)
                        Set-Acl "$($StaffIntranetFolder)\$($data_item[0])" $IntranetACL
                                        
                    #Create staff Staffnet folder and set permissions
                    New-Item "$($StaffStaffnetFolder)\$($data_item[0])" -type directory
                        $StaffnetRights = [System.Security.AccessControl.FileSystemRights]"$($StaffStaffnetPerm)"
                        $StaffnetUser = New-Object System.Security.Principal.NTAccount("domain\$($data_item[0])")
                        $StaffnetType =[System.Security.AccessControl.AccessControlType]::Allow
                        $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit" 
                        $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None 
                        $PermissionRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($StaffnetUser,$StaffnetRights,$InheritanceFlag,$PropagationFlag,$StaffnetType)
                        $StaffnetACL = Get-Acl "$($StaffStaffnetFolder)\$($data_item[0])"
                        $StaffnetACL.SetAccessRule($PermissionRule)
                        Set-Acl "$($StaffStaffnetFolder)\$($data_item[0])" $StaffnetACL
                    
                    #Create staff Submit folder and set permissions
                    New-Item "$($StaffSubmitFolder)\$($data_item[0])" -type directory
                        $SubmitRights = [System.Security.AccessControl.FileSystemRights]"$($StaffSubmitPerm)"
                        $SubmitUser = New-Object System.Security.Principal.NTAccount("domain\$($data_item[0])")
                        $SubmitType =[System.Security.AccessControl.AccessControlType]::Allow
                        $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"
                        $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None 
                        $PermissionRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($SubmitUser,$SubmitRights,$InheritanceFlag,$PropagationFlag,$SubmitType)
                        $SubmitACL = Get-Acl "$($StaffSubmitFolder)\$($data_item[0])"
                        $SubmitACL.SetAccessRule($PermissionRule)
                        Set-Acl "$($StaffSubmitFolder)\$($data_item[0])" $SubmitACL
                    
                    #Create staff Reports folder and set permissions
                    New-Item "$($StaffReportsFolder)\$($data_item[0])" -type directory
                        $ReportsRights = [System.Security.AccessControl.FileSystemRights]"$($StaffReportsPerm)"
                        $ReportsUser = New-Object System.Security.Principal.NTAccount("domain\$($data_item[0])")
                        $ReportsType =[System.Security.AccessControl.AccessControlType]::Allow
                        $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit" 
                        $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None 
                        $PermissionRule = New-Object System.Security.AccessControl.FileSystemAccessRule ($ReportsUser,$ReportsRights,$InheritanceFlag,$PropagationFlag,$ReportsType)
                        $ReportsACL = Get-Acl "$($StaffReportsFolder)\$($data_item[0])"
                        $ReportsACL.SetAccessRule($PermissionRule)
                        Set-Acl "$($StaffReportsFolder)\$($data_item[0])" $ReportsACL
                    
                    #Wait for the user to be created
                    Start-Sleep -s 2
                    
                    #Enable Exchange Mailbox
                    Enable-Mailbox -Identity "domain\$($data_item[0])" -Database $StaffMailDatabase
                        
                    #Wait for the mailbox to be created
                    Start-Sleep -s 2
                        
                    #Add User to security groups
                    do
                    {
                        $UserToGroup = Get-ADUser -identity "CN=$($data_item[0]),$($StaffADContainer)"
                        Start-Sleep -s 1
                    }
                    while ($UserToGroup -eq $null)
                    Add-ADGroupMember -identity Staff -members $UserToGroup -server server.domain.com.au
                    Add-ADGroupMember -identity Staff-NonCASES -members $UserToGroup -server server.domain.com.au
                    Add-ADGroupMember -identity Apple-Staff -members $UserToGroup -server server.domain.com.au
                    clear-variable UserToGroup
                }
            }
        }
    }
}

As I said earlier, the issue is that due to a syntax change in PowerShell v3, column values in the Table cannot be called using data_item[column number] anymore.

Monday, November 4, 2013 4:51 PM

on my computer it appears that a "syntax change" has made this:

foreach ($data_item[0] in $data.Rows)

invalid, which it always should have been. Try changing all occurrences of "$data_item[0]" to just "$data_item" to see if that works.

Al Dunbar -- remember to 'mark or propose as answer' or 'vote as helpful' as appropriate.

Monday, November 4, 2013 11:32 PM

If I use $data_item without the [0], there is no way to refrence a specific column, the reason for the value inside the brackets.

I have tried data.item[0], data[0], data_columnname eg. data_samAccountName and so on.

The error seems to occure after "data" so the underscore seems to be the issue when trying to refrence a column in this way.

Regardless of what I put inside the brackets, such as data[samAccountName] or data[0], this has no affect.

Tuesday, November 5, 2013 12:14 AM | 3 votes

If I use $data_item without the [0], there is no way to refrence a specific column, the reason for the value inside the brackets.

I have tried data.item[0], data[0], data_columnname eg. data_samAccountName and so on.

The error seems to occure after "data" so the underscore seems to be the issue when trying to refrence a column in this way.

Regardless of what I put inside the brackets, such as data[samAccountName] or data[0], this has no affect.

What happens if you do this?

foreach ($dateRow in $data.Rows)
{   
    if ([ADSI]::Exists('LDAP://CN={0},{1}' -f $dateRow['FirstColumnName'], $StaffADContainer))
    {
        $samAccountName = $dateRow['FirstColumnName']
        $Surname = $dateRow['SecondColumnName']
        $FirstName = $dateRow['ThirdColumnName']        
    }
}