Dealing with arrays...

Hi,

I am getting multiple columns for multiple employees from an Oracle database. I am creating a psObject array but I have not been able to figure out how to then run through that array to make changes in AD for each person. This is what I am doing:

$result=@()

$result = $SelectDataTable | foreach {
$row = $_; 
new-object psObject -Property @{ 
UserName = $SelectDataTable.GetString(2)
Title = $SelectDataTable.GetString(3)
Department = $SelectDataTable.GetString(4)
}
}

Then, I will take this information and set attributes in AD.

I have been trying to figure out how to run through the array with multiple values for each person. Right now I’d like to be able to just print out the values as I run through each object.

Would someone please point me in the right direction? I have been trying to figure this out for awhile, and am now finally asking for help!

Thanks

You need to refer to $_ or $row as you prefer (as that’s what you defined) when constructing the PSObject. I’d also generally recommend against using New-Object for any decently-large dataset – it’s slower than alternatives. One possible way to go:

$result = $SelectDataTable | ForEach-Object {
    [PSCustomObject]@{ 
        UserName   = $_.GetString(2)
        Title      = $_.GetString(3)
        Department = $_.GetString(4)
    }
}

However, depending on what type of object $SelectDataTable is, you may or may not be easily able to iterate over it with that cmdlet directly.

Thanks. I am still not sure how to reference each string from $result. Is it $result.username, $result.Title ? I guess not, as that I don’t think has worked for me.

Thanks

 

I’m not sure what kind of object you’re working with, so I couldn’t say for sure. A couple ways to see what you have available:

$Row | Get-Member

$Row | Format-List *

Great info, thanks. They are strings. I’ll keep plugging away here.

Getting super frustrated with this, can anyone help a bit more?
Thanks!

Does this do anything for you? I don’t know how to make a SelectDataTable.

$csv = @'
name,address,phone
joe,here,1
james,there,2
john,over there,3
'@

$data = $csv | convertfrom-csv

$result = $data | foreach-object {
  [pscustomobject]@{
    id = $_.name
    loc = $_.address
    mobile = $_.phone
  }
}

$result | foreach-object {
  write-output $_.id
  write-output $_.loc
  write-output $_.mobile
}

joe
here
1
james
there
2
john
over there
3

Thanks Everyone. I ended up using the idea from js in this forum. The important part of the script is shown below. The last part I am struggling with is the fact that I need to have the street address be a combo of some of the variables - I want to do this right in the set-aduser command, but need the address lines to be on different lines. If anyone has an idea, please let me know. I’ll keep at it!

$result = $SelectDataTable | foreach-object {
[pscustomobject]@{
Identity = $_.GetString(2)
Title = $_.GetString(3)
Department = $_.GetString(4)
MSC = $_.GetString(5)
Office_Location = $_.GetString(6)
Office_Phone = $_.GetString(7)
Supr_username = $_.GetString(10)

}
}

$result2 = $result | select -last 2

$ErrorActionPreference = "continue"

$result2 | foreach-object {
$ID = $_.identity
$usertitle = $_.title
$dept = $_.department
$mailstop = $_.msc
$location = $_.Office_Location
$phone = $_.Office_Phone
$manager = $_.Supr_username

set-aduser  -Identity $id -Title $usertitle -Department $dept -office $location -streetaddress $all  $mailstop  $location  $dept $comp

 

You can join the variables together into a string like this:

"$all $mailstop $location $dept $comp"

I had tried the cr line feed in between the variables, and that did not work. However, getting your idea about the quotes solved my problem. I was able to tuck the characters in there, thanks!

For anyone else struggling with this, here is what worked:

-streetaddress "$all`r`n$mailstop`r`n$location`r`n$dept`r`n$comp"

Or

"$all 
$mailstop 
$location 
$dept 
$comp"

Argh, now I am stuck on something else. I didn’t realize that in my script if any of the columns are null from SQL it ignores the entire row due to an exception message in powershell - Exception calling “GetString” with “1” argument(s): “Unable to cast object of type ‘System.DBNull’ to type ‘System.String’.” …

I have been trying different things all day and have come up short. Does anyone have any ideas? Thank you!

$result = $SelectDataTable | foreach-object {
[pscustomobject]@{
Identity = $_.GetString(2)
Title = $_.GetString(3)
Department = $_.GetString(4)
MSC = $_.GetString(5)
Office_Location = $_.GetString(6)
Office_Phone = $_.GetString(7)
Supr_username = $_.GetString(10)

}
}

If a property of your object is null, you could cast it to an empty or predefined ‘N/A’ string. You could do that in your foreach.

[pre]

$result = $SelectDataTable | foreach-object {
If ($null -eq $) { $ = ‘N/A’ }

[pscustomobject]@{
    Identity        = $_.GetString(2)
    Title           = $_.GetString(3)
    Department      = $_.GetString(4)
    MSC             = $_.GetString(5)
    Office_Location = $_.GetString(6)
    Office_Phone    = $_.GetString(7)
    Supr_username   = $_.GetString(10)

}

}
[/pre]