Suppose you are updating AD accounts using values from a CSV file, easy, right?
Now, let’s say that some of the fields in the CSV file are empty. E.g.:
samAccountName,Title,Department
test1,,Accounting
test2,Developer,
test3,,Pre-Sales
Question: What will happen when we apply this to AD?
Import-Csv c
update.csv | Set-QADUser -Identity { $_.samAccountName }
Answer: Set-QADUser will make the missing fields blank:
C:\> Get-QADUser -SamAccountName test[1-3] | ft samAccountName, Title,Department -AutoSize
SamAccountName Title Department-------------- ----- ----------test1 Accountingtest2 Developertest3 Pre-Sales
This is great because you can use CSV to make any updates including clearing attributes.
Now, what if you actually need a different behavior and would like to skip the missing attributes instead?
Here’s the script with which I came up:
foreach ( $record in (Import-Csv c:\update.csv)) { $command = "Set-QADUser $($record.samAccountName)" foreach ( $attr in (Get-Member -InputObject $record -MemberType NoteProperty) ) { $value = $record.($attr.Name) if ( $value -and ( $attr.Name -ne 'samAccountName' ) ) { $command += " -$($attr.Name) $value" } } Invoke-Expression $command }
Basically, I:
- Go through each record in the CSV file.
- For each record ($record) I iterate through its attributes ($attr – columns in the CSV).
- If the column is not samAccountName and is not empty, I add it to the string ($command) in which I construct the Set-QADUser command.
- Then I simply run the command with Invoke-Expression.
You can load the script in the PowerGUI Script Editor and press F11 to go step-by-step and see how it works.
Tags: AD, AD cmdlets, Active Directory, Examples, PowerShell, cmdlets
Subscribe by email
Dmitry's Blog: Cloud, PowerShell and beyond 
I like the approach of this and have used a similar solution in powershell and VBScript (using ADO) however I have always had the problem of imbedded spaces and quote characters. Do you have any thoughts as to a more elegant solution than appending a single quote to the beginning and end of each variable (which does not get around the problem of a department, for example, that contains “Plasterer’s Supplies”. My basic amendment would be $command += ” -$($attr.Name) ‘$value'”.
Stuart,
Sure, if you don’t like strings with calculated expressions, just use string concatenation instead:
$command += ” -” + $attr.Name + ” ” + $value
Dmitry
Sorry, not complaining about the structure of the code, I am querying whether there is a neater way of handling troublesome charactes in the data. If you change the CSV file to look like this:
samAccountName,Title,Department
test1,,Accounting
test2,Developer,
test3,,Plasterer’s Supplies
(Note that is a single quote after Plasterer – WordPress keeps trying to make it look pretty).
I think that if you run that, your script will break. It is not just the QAD cmdlets either – it is anything that uses invoke-expression. See my example below having copied the data above into test.csv:
PS C:\Users\swhite26> $x = import-csv test.csv
PS C:\Users\swhite26> $x = import-csv test.csv
PS C:\Users\swhite26> $y = $x[2]
PS C:\Users\swhite26> invoke-expression “dir $($y.department)”
Invoke-Expression : Incomplete string token.
At line:1 char:18
+ invoke-expression <<<
Stuart,
OK, sorry, I was not reading carefully enough…
Yes, I guess you could add the single quotes to fight spaces, etc. and use Replace to escape the single quotes inside:
cls
cd c:\Temp
$s = “Plasterer’s Supplies”
$s = $s.Replace(“‘”, “””)
$command = “dir ”
$command += “‘$s'”
$command
Invoke-Expression $command
Dmitry