Archive for October 7th, 2008

Skip empty CSV fields

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                    Accounting
test2          Developer           
test3                    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:

  1. Go through each record in the CSV file.
  2. For each record ($record) I iterate through its attributes ($attr – columns in the CSV).
  3. 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.
  4. 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: , , , , ,


Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

October 2008
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728293031  

%d bloggers like this: