by allenr74 at 2012-11-05 06:36:08
Hello,by poshoholic at 2012-11-05 06:50:02
I have a .CSV file as a source file and I need to import information into an AD multi-value field. The imported data needs to be as a true multi-value field and not just a single string.
I conducted the following test import in my lab into the multi-value field (the field was added to our AD schema and is not a default field) and it was successful:
Add-PSSnapin Quest.ActiveRoles.ADManagement
$rraSector="test1;test2"
$split=$rraSector.split(";")
get-qaduser -samAccountName testuser | set-qaduser -ObjectAttributes @{rraSector=@{Update=@($split[0])}} | set-qaduser -ObjectAttributes @{rraSector=@{Append=@($split[1])}}
As you can see, I am using the Quest Active Roles management tool for assistance.
The line that begins with "get-qaduser" is actually one line. That successfully updated the field with 2 values.
Now, I would like to integrate the above into the following command that I use to import other data into AD:
Add-PSSnapin Quest.ActiveRoles.ADManagement
$OU = "domain/Users and Workstations"
Import-csv -path E:\data\powershell\Excel\bluebook032212.csv | Foreach-Object {Get-QADUser -SearchRoot $OU -LastName $.LastName | Set-QADUser -objectAttributes @{rraSector=$.Sector;rraPractice=$.Practice}}
I need to perform the "split" operation in the $.Sector and $.Practice fields of the CSV file IF a ; is present. Then I would need to import the result of the split to each user account so I get the correct multi-value into AD.
Can anyone help me with this command formatting?
If you use the -split operator, that will only perform the split if there is a character to split on. With that in mind, it sounds to me like you want something like this:by allenr74 at 2012-11-05 08:15:01@{rraSector=@($.Sector -split ';');rraPractice=@($.Practice -split ';')}
I wrap the results of the split in an array so that you pass an array of strings into these parameter assignments, even if there is only one string in the collection.
This is untested, but it’s the first thing I would try. Give that a go and let me know how it works out for you.
Right. I want to split on ;by allenr74 at 2012-11-05 09:15:34
I will definitely give that a shot and let you know! Thanks!
As I was formatting the command, I have another question…by poshoholic at 2012-11-05 09:48:20
I would like to put the information before the ; in as an "UPDATE" for the multi-value field and the information after the ; as an "APPEND". Example:
test1;test2
$split=$rraSector.split(";")
get-qaduser -samAccountName testuser | set-qaduser -ObjectAttributes @{rraSector=@{Update=@($split[0])}} | set-qaduser -ObjectAttributes @{rraSector=@{Append=@($split[1])}}
That is the only way the field will be update with 2 values (or more if needed) rather one long string with a ";" in it.
I don’t think you can specify update vs append when setting object attributes like that with Set-QADUser. Instead you should get the current attribute value, and then update or append that value depending on what you get from your csv file input, then push the updated/appended value back using Set-QADUser.by allenr74 at 2012-11-06 07:02:57
Yes, update and then appending does work. I did that in my example above. I’m just struggling with how to split the information in the Sector field of the .CSV file and then put the first part of the split in the UPDATE function and then the second part of the split in the APPEND function.by allenr74 at 2012-12-05 07:50:35
Thanks, everyone!by ArtB0514 at 2012-12-05 09:21:42
I got the following to work and import the data:
foreach ($User in (Import-csv -path E:\data\powershell\Excel\HR_EMPLID_EXP.csv -Delimiter "|")) {
$Sector = $User.Sector -split ";"
$Practice = $User.Practice -split ";"
Get-QADUser -ObjectAttributes @{employeeID=$User.Employeeid} | Set-QADUser -ObjectAttributes @{rraSector = $Sector;rraPractice = $Practice}
}
However, my hr department stated that the order in which the data that is imported from the .CSV file into the rraSector and rraPractice fields should not change. However, during the import, it does change without me doing anything.
Here is how the data is presented in the .CSV column for a user:
Commercial & Retail Banking;Financial Officers;Asset & Wealth Management;Portfolio Company
It is entered into AD and appears this way:
Portfolio Company
Asset & Wealth Management
Financial Officers
Commercial & Retail Banking
It appears that the array is imported into AD from last to first? How can I prevent that? Using Sort-object and then reversing the array order? If so, how/where would that fit into the script?
Answered over in PowerGUI.org:
Since AD is inserting the data in reversed order, just reverse the data from the CSV column.$Data = "Commercial & Retail Banking;Financial Officers;Asset & Wealth Management;Portfolio Company" | split ";"
$Sector = @()
($Data.Count-1)…0 | foreach {$Sector += $Data[$]