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

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) }
BYRONJACKSONAsked:
Who is Participating?
 
oBdACommented:
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
 
becraigCommented:
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
 
oBdACommented:
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
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.

 
BYRONJACKSONAuthor Commented:
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
 
oBdACommented:
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
 
BYRONJACKSONAuthor Commented:
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
 
BYRONJACKSONAuthor Commented:
NewUsers1.csvNewUsers1.csvNewUsers1.csvAttached is my test user i have been playing with, the main csv has about 500+ entries hope this helps
0
 
oBdACommented:
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
 
BYRONJACKSONAuthor Commented:
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
 
BYRONJACKSONAuthor Commented:
Brilliant all working!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.