Import blank/empty fields to Active Directory from CSV

Welcome Forums General PowerShell Q&A Import blank/empty fields to Active Directory from CSV

Viewing 12 reply threads
  • Author
    Posts
    • #228715
      Participant
      Topics: 2
      Replies: 5
      Points: 37
      Rank: Member

      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:

      <span style="color: #0000ff">$csv = Import-CSV ".\contacts.csv" | Where-Object { $_.PSObject.Properties.Value -ne $null}</span>

      <span style="color: #0000ff">$Counter = 1</span>

      <span style="color: #0000ff">foreach ($line in $CSV){</span>

      <span style="color: #0000ff">$list = @{</span>
      <span style="color: #0000ff">givenName = $line.'givenName'</span>
      <span style="color: #0000ff">sn = $line.'sn'</span>
      <span style="color: #0000ff">displayName = $line.'cn'</span>
      <span style="color: #0000ff">Company = $line.'Company'</span>
      <span style="color: #0000ff">Department = $line.'Department'</span>
      <span style="color: #0000ff">Title = $line.'Title'</span>
      <span style="color: #0000ff">StreetAddress = $line.'StreetAddress'</span>
      <span style="color: #0000ff">l &lt;#City#&gt; = $line.'City'</span>
      <span style="color: #0000ff">st = $line.'st'</span>
      <span style="color: #0000ff">PostalCode = $line.'PostalCode'</span>
      <span style="color: #0000ff">co = $line.'co'</span>
      <span style="color: #0000ff">mobile = $line.'mobile'</span>
      <span style="color: #0000ff">telephoneNumber = $line.'telephoneNumber'</span>
      <span style="color: #0000ff">facsimileTelephoneNumber = $line.'facsimileTelephoneNumber'</span>
      <span style="color: #0000ff">homePhone = $line.'homePhone'</span>
      <span style="color: #0000ff">pager = $line.'pager'</span>
      <span style="color: #0000ff">assistant = (get-aduser $line.'assistant').distinguishedname #you can query AD for the distinguishedname</span>
      <span style="color: #0000ff">mail = $line.'mail'</span>
      <span style="color: #0000ff">Notes = $line.'Notes'</span>
      <span style="color: #0000ff">wwwhomepage = $line.'wwwhomepage'</span>
      <span style="color: #0000ff">}</span>
      <span style="color: #0000ff">New-ADObject -Type Contact -Name $line.cn -OtherAttributes $list -Path "OU=PFContacts,OU=Contacts,OU=Global,DC=Sample,DC=Com" -verbose</span>
      <span style="color: #0000ff">$counter++</span>
      <span style="color: #0000ff">}</span>

       

       

    • #228817
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

      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 &quot;.\contacts.csv&quot; | Where-Object { ![string]::IsNullOrWhiteSpace($_.PSObject.Properties.Value) } | Measure-Object
      Import-CSV &quot;.\contacts.csv&quot; | 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:
      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.
    • #228835
      Participant
      Topics: 2
      Replies: 5
      Points: 37
      Rank: Member

      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 http://www.test.com
      facsimileTelephoneNumber (999) 888-8888 
      [email protected]
      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

       

    • #228841
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

      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.

      • This reply was modified 6 months, 2 weeks ago by Dave Carroll. Reason: add otherattribute param info
    • #228865
      Participant
      Topics: 2
      Replies: 5
      Points: 37
      Rank: Member

      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 http://www.test.com
      telephoneNumber (888) 888-8888 
      facsimileTelephoneNumber (888) 888-8888 
      mail [email protected]
      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

       

    • #228979
      Participant
      Topics: 2
      Replies: 5
      Points: 37
      Rank: Member

      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

       

    • #229012
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

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

      I gave you incorrect information regarding the Country (c) 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.

      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
    • #229034
      Participant
      Topics: 9
      Replies: 678
      Points: 2,683
      Helping Hand
      Rank: Community Hero

      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.

      • This reply was modified 6 months, 2 weeks ago by Doug Maurer.
      • This reply was modified 6 months, 2 weeks ago by Doug Maurer.
    • #229037
      Participant
      Topics: 9
      Replies: 678
      Points: 2,683
      Helping Hand
      Rank: Community Hero

      Yeah I don’t know. Maybe they changed something. :/  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!

    • #229921
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

      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.

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

    • #229924
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

      Yeah I don’t know. Maybe they changed something. :/ 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!

      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.

    • #229927
      Participant
      Topics: 2
      Replies: 8
      Points: 100
      Rank: Participant

      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

      Just curious how things are going with the script.

      Thanks,

      Dave

    • #229930
      Participant
      Topics: 2
      Replies: 5
      Points: 37
      Rank: Member

      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.

       

      • This reply was modified 6 months, 2 weeks ago by sonacanada65.
Viewing 12 reply threads
  • The topic ‘Import blank/empty fields to Active Directory from CSV’ is closed to new replies.