Solved

Hello, i have an issue with a script to import and update a list of current user details in AD

Posted on 2014-09-03
10
432 Views
Last Modified: 2014-09-05
Hello,
I have a csv file that has been exported from our AD, I have amended and added details and can re-import a sample few back into a test environment, however I have come across an issue where any blank cells in the csv file cause the script to fall over.

is there a way I can get my script to skip or ignore any blank cells it comes across, change the value to empty if needed and continue?

here is what I have so far which works fine if all csv cells have information in them.



# Import AD Module            
Import-Module ActiveDirectory            
           
# Import CSV into variable $userscsv            
         
$users = Import-Csv -Path c:\users\aflook\desktop\NewUsers1.csv            
# Loop through CSV and update users if the exist in CVS file            
           
foreach ($user in $users)
{            
#Search in specified OU and Update existing attributes            
 
 Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase “ou=Adam Test,DC=therange,DC=local" |            
  Set-ADUser -Description $($user.Description) -Office $($user.Office) -GivenName $($user.GivenName) -SurName $($user.SurName) -DisplayName $($user.DisplayName) -PostalCode $($user.postalCode) -mobile $($user.mobile) -Company $($user.company) -StreetAddress $($user.StreetAddress) -City $($user.City) -OfficePhone $($user.OfficePhone) -Title $($user.Title) -Department $($user.Department) -Fax $($user.Fax) -State $($user.State) -HomePage $($user.HomePage) }
0
Comment
Question by:BYRONJACKSON
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40302419
Hi I think we can look at the recently answered question:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_28505620.html

Simply make the change to do the following:

It is cumbersome but does what you need.

import-csv file.csv | % {
if ($_.value1 -eq $null -or $_value1 -eq "") {$_.value1 = "EMPTY"}
if ($_.value2 -eq $null -or $_value1 -eq "") {$_.value2 = "EMPTY"}
if ($_.value3 -eq $null -or $_value1 -eq "") {$_.value3 = "EMPTY"}
}

Open in new window

0
 
LVL 84

Expert Comment

by:oBdA
ID: 40302812
You can do that dynamically by querying the properties of the imported csv files, generating a hashtable from it, and then use splatting (see http://technet.microsoft.com/en-us/library/jj672955.aspx):
# Import AD Module
Import-Module ActiveDirectory

# Import CSV into variable $userscsv

$users = Import-Csv -Path "C:\users\aflook\desktop\NewUsers1.csv"
# Loop through CSV and update users if the exist in CVS file

foreach ($user in $users)
{
	#Search in specified OU and Update existing attributes            
	$Arguments = @{}
	$user | Get-Member -MemberType NoteProperty | % {if ($user.($_.Name)) {$Arguments[$_.Name] = $user.($_.Name)}}
	Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "ou=Adam Test,DC=therange,DC=local" | Set-ADUser @Arguments
}

Open in new window

0
 

Author Comment

by:BYRONJACKSON
ID: 40302879
Hi thanks for the replies i have tested the splatting method and this is the error i now receive.

any help would be greatly appreciated.

Set-ADUser : Cannot convert 'System.String' to the type 'System.Management.Automation.SwitchParameter' required by parameter 'Confirm'.
At line:14 char:143
+ ... " | Set-ADUser @Arguments
+                    ~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.ActiveDirectory.Management.Commands.SetADUser
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 84

Expert Comment

by:oBdA
ID: 40302887
Does the csv file have more fields than the ones you've used in the Set-ADUser? If so, please post a sanitized version of the csv file (one content line should be enough).
0
 

Author Comment

by:BYRONJACKSON
ID: 40302889
I have also looked at this one,
import-csv file.csv | % {
if ($_.value1 -eq $null -or $_value1 -eq "") {$_.value1 = "EMPTY"}
if ($_.value2 -eq $null -or $_value1 -eq "") {$_.value2 = "EMPTY"}
if ($_.value3 -eq $null -or $_value1 -eq "") {$_.value3 = "EMPTY"}
}

do i need to pre define the values or can i simply input them where it states "value1" etc, and will this on its own carry out the task or do i need to an in other sections from my current code?

many thanks
0
 

Author Comment

by:BYRONJACKSON
ID: 40302894
NewUsers1.csvNewUsers1.csvNewUsers1.csvAttached is my test user i have been playing with, the main csv has about 500+ entries hope this helps
0
 
LVL 84

Expert Comment

by:oBdA
ID: 40302935
The issue is the "co" column. Since there is no AD property "co", Set-ADUser tries to match that to a known argument and finds "-Confirm".
If you want to set the "Country" property, simply rename the "co" column header to "Country".
If not, this could be filtered out as well; would you then rather use a blacklist (to exclude a given list columns) or a whitelist (to include only a given list of columns)?
Note that in its current form, the script will fail or create incorrect results if any property in the file contains a comma (like "Main Street 123, Apartment 456"). The fields should be enclosed in double quotes to prevent that.
0
 

Author Comment

by:BYRONJACKSON
ID: 40302956
Ah ok brilliant, so for the Blank fields where i need data to be either removed from AD if its not present or left blank do i need to put "" into the csv's blank cell?
i would rather use a white list to include the given columns, will this cause an issue with multiple entries though as not all columns are completely empty. basically it needs to check for an empty cell in the column and either ignore or correct the entry in AD and then carry on if that makes sense?
0
 
LVL 84

Accepted Solution

by:
oBdA earned 500 total points
ID: 40303152
Currently, it simply ignores properties that don't have a value.
This one can exclude columns, and it will clear the properties that don't have a value:
# Import AD Module
Import-Module ActiveDirectory

# Import CSV into variable $userscsv

$users = Import-Csv -Path "C:\users\aflook\desktop\NewUsers1.csv"
# Loop through CSV and update users if the exist in CSV file
$ExcludeProperties = @(
	"co"
)

foreach ($user in $users)
{
	$Arguments = @{}
	$user | Get-Member -MemberType NoteProperty | ? {$ExcludeProperties -NotContains $_.Name} | % {if ($user.($_.Name)) {$Arguments[$_.Name] = $user.($_.Name)} Else {$Arguments[$_.Name] = $Null}}
	#Search in specified OU and Update existing attributes            
	Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "ou=Adam Test,DC=therange,DC=local" | Set-ADUser @Arguments
}

Open in new window

And even without quotes, blank cells are not an issue in the csv; only fields that contain a comma in their text will be.
0
 

Author Closing Comment

by:BYRONJACKSON
ID: 40305494
Brilliant all working!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
A procedure for exporting installed hotfix details of remote computers using powershell
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question