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: , , , , ,

4 Responses to “Skip empty CSV fields”


  1. 1 Stuart October 7, 2008 at 11:24 am

    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'”.

  2. 2 Dmitry Sotnikov October 7, 2008 at 11:27 am

    Stuart,

    Sure, if you don’t like strings with calculated expressions, just use string concatenation instead:

    $command += ” -” + $attr.Name + ” ” + $value

    Dmitry

  3. 3 swhite26 October 7, 2008 at 11:46 am

    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 <<<

  4. 4 Dmitry Sotnikov October 7, 2008 at 12:19 pm

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

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
« Sep   Nov »
 12345
6789101112
13141516171819
20212223242526
2728293031  

%d bloggers like this: