Import blank/empty fields to Active Directory from CSV

Hi,

I am trying to import from a csv file that contains 4000 lines with several blank fields and trying below script, I am not able to get rid of the emmty fields and code complains about the empty fileds:

Error:

“New-ADObject : Cannot validate argument on parameter ‘OtherAttributes’. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.”

Script that I am using:

$csv = Import-CSV “.\contacts.csv” | Where-Object { $_.PSObject.Properties.Value -ne $null}

$Counter = 1

foreach ($line in $CSV){

$list = @{
givenName = $line.‘givenName’
sn = $line.‘sn’
displayName = $line.‘cn’
Company = $line.‘Company’
Department = $line.‘Department’
Title = $line.‘Title’
StreetAddress = $line.‘StreetAddress’
l <#City#> = $line.‘City’
st = $line.‘st’
PostalCode = $line.‘PostalCode’
co = $line.‘co’
mobile = $line.‘mobile’
telephoneNumber = $line.‘telephoneNumber’
facsimileTelephoneNumber = $line.‘facsimileTelephoneNumber’
homePhone = $line.‘homePhone’
pager = $line.‘pager’
assistant = (get-aduser $line.‘assistant’).distinguishedname #you can query AD for the distinguishedname
mail = $line.‘mail’
Notes = $line.‘Notes’
wwwhomepage = $line.‘wwwhomepage’
}
New-ADObject -Type Contact -Name $line.cn -OtherAttributes $list -Path “OU=PFContacts,OU=Contacts,OU=Global,DC=Sample,DC=Com” -verbose
$counter++
}

 

 

I think your Where-Object clause is only omitting those records that have all fields $null and does not consider empty strings.

Try the following:

Import-CSV ".\contacts.csv" | Where-Object { ![string]::IsNullOrWhiteSpace($_.PSObject.Properties.Value) } | Measure-Object
Import-CSV ".\contacts.csv" | Measure-Object
I suspect it would be the same count.
Ideally, you would need to build the $list per record/contact.
Something like the following:
$csv = Import-CSV ".\contacts.csv" | Where-Object { ![string]::IsNullOrWhiteSpace($_.PSObject.Properties.Value) 

foreach ($Contact in $csv) {
$Attributes = @{}
foreach ($Property in $Contact.PSObject.Properties) {

# skip cn, since it will be used to create the contact
if ($Property.Name -eq ‘cn’) {
continue
}

# if the property is a string, add it to hashtable if not null or only whitespace ‘’ or ’ ’
if ($Property.TypeNameOfValue -eq ‘System.String’) {
if (-Not [string]::IsNullOrWhiteSpace($Property.Value)) {
$Attributes.add($Property.Name,$Property.Value)
}

# if the property is not a string (not sure when this would happen), add it to hashtable if it’s not null
} else {
if ($null -ne $Property.Value) {
$Attributes.add($Property.Name,$Property.Value)
}
}
}

$Contact.cn
$Attributes

‘-’ * 40

#New-ADObject -Type Contact -Name $Contact.cn -OtherAttributes $Attributes -Path “OU=PFContacts,OU=Contacts,OU=Global,DC=Sample,DC=Com” -Verbose
#$counter++
}

It is good to name variables something meaningful. I took the liberty to show what I would call the variables. Ultimately, it's up to you what you want to call them.
The snippet as-is should output each contact CN, then all fields with non-whitespace, then a line.
Also, you will need to ensure that all attributes names in the CSV is indeed using the ldapDisplayName value, but I think you've got this covered - I learned that between ADUser and ADContact the Notes field is backed by a different field.
Hope that helps.
Good luck.

Hello Dave,

The good news is that your modification was great for the records that did not have a country, however for every line with Country property, I can see below errors.

What I used as a country is “co” as “country” itself has been troublesome in the past. Any suggestions for this issue?

error:

XXX XXX
PostalCode xxx-xxx
telephoneNumber (999) 888-8888
State BC
Wwwhomepage www.test.com
facsimileTelephoneNumber (999) 888-8888
test@test.com
L Vancouver
givenname xxx
Company xxxx Communications Inc.
Streetaddress 999 Kingsway
Country Canada
sn xxxx

VERBOSE: Performing the operation “New” on target “cn=xxx xxxx,OU=PFContacts,OU=Contacts,OU=Global,DC=test,DC=com”.
New-ADObject : The specified directory service attribute or value does not exist
Parameter name: Country
At C:\TEMP\o365\PFContacts_to_ADContacts(rev1.4).ps1:30 char:5
+ New-ADObject -Type Contact -Name $Contact.cn -OtherAttributes $Attributes -P …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (cn=xxx xxx,OU=…test,DC=com:String) [New-ADObject], ArgumentException
+ FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.NewADObject

 

Looking at the Contact AD schema, we see the following two attributes related to Country:

CN ldapDisplayName Examples
Country-Code countryCode US, UK
Country-Name c United States, United Kingdom
Be sure to check all of your CSV fields to ensure they are actual valid ldapDisplayName of attributes. The OtherAttributes parameter for New-ADObject specifies that ldapDisplayName must be used. Other AD cmdlets, like New-ADUser, may have other parameters that can use the CN, ldapDisplayName, or even attribute alias (Comment vs Info vs Notes).

Good luck again. Seems like you’re really close now.

Hi Dave,

There are still a few issues:

1-For some strange reasons, and once I changed the country to “C” and state to “st”, All the lines that have those values are dropped however the other ones can be created in AD.(see below sample error), the error this time is complaining about the acceptable range, but BC and Canada should be accepted for this case!?

2- I have renamed the “cn” properties to “displayname” , although I get no errors, but “display name value” is still blank on the AD side.

Errors:

XXX XXX
st BC
PostalCode x1x-x1x
Wwwhomepage www.test.com
telephoneNumber (888) 888-8888
facsimileTelephoneNumber (888) 888-8888
mail xxx@test.com
L Vancouver
givenname xzxx
Company Test Communications Inc.
Streetaddress 999 Kingsway
C Canada
sn xxxx

VERBOSE: Performing the operation “New” on target “cn=xxx xxxx,OU=PFContacts,OU=Contacts,OU=Global,DC=test,DC=com”.
New-ADObject : A value for the attribute was not in the acceptable range of values
At C:\TEMP\o365\PFContacts_to_ADContacts(rev1.4).ps1:30 char:5
+ New-ADObject -Type Contact -Name $Contact.displayname -OtherAttributes $Attr …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (cn=Jaz Mann,OU=…test,DC=com:String) [New-ADObject], ADException
+ FullyQualifiedErrorId : ActiveDirectoryServer:8322,Microsoft.ActiveDirectory.Management.Commands.NewADObject

 

Updates:

1- I have removed all the values in the “C” column and I get no errors now.(great but I need to find a solution)

2- I have removed "# skip cn, since it will be used to create the contact and changed “CN” to “displayname” " from the code, and now I have all the display names populated properly.

The only remaining issue is the Country now, not sure why adding “C” with standard country names such as Canada, the United States is troublesome as running the script shows below errors saying:

New-ADObject : A value for the attribute was not in the acceptable range of values

 

I thought I had already replied, but it seems I’ve lost it.

I gave you incorrect information regarding the Country © value. Based on the AD Schema page, it actually takes the 2 character ISO-3166 country code, e.g. US, CA, GB.

Here is one option that will validate the Country value.

$TwoLetterISORegionNames = Get-Culture -ListAvailable |
    Foreach-Object {
        try { [System.Globalization.RegionInfo]::new($_.Name) }
        catch {}
    } |
    Select-Object -ExpandProperty TwoLetterISORegionName | Sort-Object -Unique

$counter = 1

:ContactLoop foreach ($Contact in $csv) {    
    $Attributes = @{}
    foreach ($Property in $Contact.PSObject.Properties) {
        if ($Property.TypeNameOfValue -eq 'System.String') {
            if (-Not [string]::IsNullOrWhiteSpace($Property.Value)) {         
                if ($Property.Name -eq 'c') {
                    if ($Property.Value -in $TwoLetterISORegionNames) {
                        $Attributes.add($Property.Name,$Property.Value)
                    } else {
                        Write-Warning -Message "Skipping contact # $counter - ${Contact.Name}"
                        continue ContactLoop
                    }
                } else {
                    $Attributes.add($Property.Name,$Property.Value)
                }                
            }
        } else {
            if ($null -ne $Property.Value) { 
                $Attributes.add($Property.Name,$Property.Value)
            }
        }
    }

    $Attributes
    #New-ADObject -Type Contact -Name $Contact.cn -OtherAttributes $Attributes -Path "OU=PFContacts,OU=Contacts,OU=Global,DC=Sample,DC=Com" -Verbose
  
    $counter++
}
It uses a loop label in order to continue from the outer (contact) loop, which I myself just learned about today.
Also, feel free to follow me on Twitter.
Thanks,
Dave

Here’s a tip for formatting code. Above the menu bars in the top right, click the “text” and then do the ‘pre’ ‘/pre’ - then when you switch back to visual you will actually have the code box. Posting from either view at that point used to avoid these dots. Took me way too long to figure this out, thought maybe it could help you guys.

Yeah I don’t know. Maybe they changed something. :confused: Interesting to see that continue can be used just like break and goto to jump to a label. I guess it makes sense but I hadn’t considered it. Very neat, Dave!

[quote quote=229034]Here’s a tip for formatting code. Above the menu bars in the top right, click the “text” and then do the ‘pre’ ‘/pre’ – then when you switch back to visual you will actually have the code box. Posting from either view at that point used to avoid these dots. Took me way too long to figure this out, thought maybe it could help you guys.

[/quote]

I tried, but I suspect it’s something in the defunct syntax highlighter.

[quote quote=229037]Yeah I don’t know. Maybe they changed something. :confused: Interesting to see that continue can be used just like break and goto to jump to a label. I guess it makes sense but I hadn’t considered it. Very neat, Dave!

[/quote]

I’ve been writing PowerShell code for over 10 years, I guess I never had to jump out of a nested loop. Named loops are pretty neat.

[quote quote=228979]Updates:
1- I have removed all the values in the “C” column and I get no errors now.(great but I need to find a solution)
2- I have removed “# skip cn, since it will be used to create the contact and changed “CN” to “displayname” ” from the code, and now I have all the display names populated properly.

The only remaining issue is the Country now, not sure why adding “C” with standard country names such as Canada, the United States is troublesome as running the script shows below errors saying:

New-ADObject : A value for the attribute was not in the acceptable range of values
[/quote]

Just curious how things are going with the script.

Thanks,

Dave

So, to be honest, the very last script was showing several new errors, and I did not wish to bother you with bunch of new errors.

what I ended up doing was to use the second script and modify my CSV to accommodate the 2 digit country codes as we had only 6 countries and editing them in the CSV was not that hard. This worked well and the results were acceptable. There were some random other issues such as extra-large info fields as they were limited to 1024 characters in AD and we had to edit them as well in the CSV to fix the issues.

Thank you again for your tips and tricks as they were really helpful.