Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
496 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 85

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 85

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 85

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 85

Accepted Solution

by:
oBdA earned 2000 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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

721 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