Go Premium for a chance to win a PS4. Enter to Win

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
?
523 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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

773 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