Announcement

Collapse
No announcement yet.

import data from excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • import data from excel

    Hello,

    Can anyone help me with a script that will bulk import of Zip Codes from excel spreadsheet into AD's Zip Code fields.

    thanks,

    Mickey

  • #2
    Re: import data from excel

    this should get you going

    http://technet.microsoft.com/en-us/l.../bb727091.aspx

    bio..

    Comment


    • #3
      Re: import data from excel

      Originally posted by Sheshd View Post
      Can anyone help me with a script that will bulk import of Zip Codes from excel spreadsheet into AD's Zip Code fields.
      hi Mickey,

      How does the layout/formatof this excel worksheet look like . And which user object attributes does it contain exactly,
      do all these users exist already in AD?


      \Rems

      This posting is provided "AS IS" with no warranties, and confers no rights.

      __________________

      ** Remember to give credit where credit's due **
      and leave Reputation Points for meaningful posts

      Comment


      • #4
        Re: import data from excel

        Originally posted by Rems View Post
        hi Mickey,

        How does the layout/formatof this excel worksheet look like . And which user object attributes does it contain exactly,
        do all these users exist already in AD?


        \Rems
        The Excel has two columns: A= UserName (that was exported from the AD) and B = Zip Code (for example).
        I want the Zip code to be imported into the ZipCode field at the User's record.
        Last edited by Ossian; 22nd January 2010, 12:06. Reason: Removed links

        Comment


        • #5
          Re: import data from excel

          Originally posted by Sheshd View Post
          The Excel has two columns: A= UserName (that was exported from the AD) and B = Zip Code (for example).
          I want the Zip code to be imported into the ZipCode field at the User's record.
          "UserName" is not an existing name of an user attribute in Active Directory. What attribute exactly was exported from AD? Or, what was the script/command-line that you used to export the user names?

          \Rems

          This posting is provided "AS IS" with no warranties, and confers no rights.

          __________________

          ** Remember to give credit where credit's due **
          and leave Reputation Points for meaningful posts

          Comment


          • #6
            Re: import data from excel

            Originally posted by Rems View Post
            "UserName" is not an existing name of an user attribute in Active Directory. What attribute exactly was exported from AD? Or, what was the script/command-line that you used to export the user names?

            \Rems
            Sorry, I mean Logon name (ADSearch Name userPribcipalName)

            Comment


            • #7
              Re: import data from excel

              Here is a sample.
              The computer running the script must have MS Excel installed on.
              The account running the script must have sufficient rights for editing the objects in AD.

              Excel file - the 1st Sheet contain the values. In Column A: UserPrincipalNames ( account@domain.local) and, in Column B: Zipcodes
              First row does not contain column titles (values start at first row).
              You can change these parameters in the script when needed.

              The script searches for UPN in the Global Catalog. Then when found the UPN is translated to the object's distinguishedName.
              (If a GC query failed for some reason, the script then perform an AD search for the UPN to obtain the distinguishedName)

              Code:
              ' Constants for the NameTranslate object.
              Const ADS_NAME_INITTYPE_GC = 3
              Const ADS_NAME_TYPE_USER_PRINCIPAL_NAME = 9
              Const ADS_NAME_TYPE_1779 = 1
              Dim objTrans, objRootDSE
              
              strInputFile = "C:\test\Map1.xls"
              
              Dim oConnection, strDNSDomain
              ' Determine DNS domain name.
              Set objRootDSE = GetObject("LDAP://RootDSE")
              strDNSDomain = objRootDSE.Get("defaultNamingContext")
              
              Set objTrans = CreateObject("NameTranslate")
              objTrans.Init ADS_NAME_INITTYPE_GC, ""
              
              ' Read from xls-file
              Set objExcel = CreateObject("Excel.Application")
              objExcel.DisplayAlerts = False
              
              On Error Resume Next
              Set objWorkbook = objExcel.Workbooks.Open(strInputFile)
              
              WorkSHEET = 1  ' read from the 1st sheet
              objworkbook.worksheets(WorkSHEET).Activate
              
              ' columns:
              Const column_UPN = 1        '(eq A)
              Const column_PostalCode = 2 '(eq B)
              
              ' start reading from row number:
              intRow = 1
              
              Do Until iCnt >100 '(close xls when the last 100 values where empty)
              
                 strUPN = Trim(objExcel.Cells(intRow, column_UPN).Value)
                 strZip = Trim(objExcel.Cells(intRow, column_PostalCode).Value)
                 If len(strUPN) > 0 Then
              
                   strUserDn = TranslateToDN(strUPN)
              
                   If trim(strUserDn) = "" then _
                     strUserDn = FindObject("user", "upn", strUPN)
              
                   If len(strUserDn) > 0 AND len(strZip) > 0 Then
                     call SetPostalCode(strUserDn, strZip)
                   End If
                   iCnt = 0
              
                 Else iCnt = iCnt+1 : End If
                 intRow = intRow + 1
              Loop
              
              objExcel.Quit
              Set objRootDSE = Nothing
              Wscript.Quit 0
              
              Sub SetPostalCode(sItem, sVal)
                 Dim objUser
                 On Error Resume Next
                 ' Bind to the Group object in Active Directory with the LDAP provider.
                 Set objUser = GetObject("LDAP://" & sItem)
                 objUser.Put "postalCode", sVal
                 objUser.SetInfo
              End Sub
              
              Function TranslateToDN(userPrincipalName)
                 ' With objTrans
                 On Error Resume Next
                 objTrans.Set ADS_NAME_TYPE_USER_PRINCIPAL_NAME, userPrincipalName
                 If Err.number = 0 Then
                   TranslateToDN = objTrans.Get(ADS_NAME_TYPE_1779)
                   ' Escape any "/" characters with backslash escape character.
                   ' All other characters that need to be escaped will be escaped.
                   TranslateToDN = Replace(TranslateToDN, "/", "\/")
                 Else
                   Err.clear
                   TranslateToDN = Empty
                 End If
                 On Error Goto 0
              End Function
              
              Function FindObject(sObjectClass, sAttribute, strVal)
                 Dim oRecordset
                 dim oc
                 Select Case LCase(sObjectClass)
                   Case "user"
                       oc = "(objectClass=user)(objectCategory=person)"
                   Case "computer"
                       oc = "(objectClass=computer)"
                   Case "group"
                       oc = "(objectClass=group)"
                   Case Else
                       oc = Empty
                 End Select
              
                 dim at
                 Select Case LCase(sAttribute)
                   Case "cn", "common name"
                       at = "(cn=" & strVal & ")"
                   Case "upn", "userprincipalName"
                       at = "(userprincipalName=" & strVal & ")"
                   Case "samacountname"
                       at = "(sAMAcountName=" & strVal & ")"
                   Case Else
                       at = Empty
                 End Select
              
                 FindObject = Empty
                 If Not IsObject(oConnection) Then
                   Set oConnection = CreateObject("ADODB.Connection")
                   oConnection.Provider = "ADsDSOObject"  'The ADSI OLE-DB provider
                 End If
              
                 Set oRecordset = CreateObject("ADODB.Recordset")
                 oConnection.Open "ADs Provider"
              
                 ' LDAP Search
                 strQuery = "<LDAP://" & strDNSDomain & ">;(&" & oc _
                   & at & ");distinguishedName;subtree"
                 On Error Resume Next
                 Set oRecordset = oConnection.Execute(strQuery)
              
                 If oRecordset.EOF And oRecordset.BOF Then
                   rem "No matching group found " & sCommonName
                 Else
                    While Not oRecordset.EOF
                     'bind to the Group object using LDAP provider
                     FindObject = oRecordset.Fields("distinguishedName").Value
                     FindObject = Replace(FindObject, "/", "\/")
                     oRecordset.MoveNext
                   Wend
                 End If
                 oConnection.Close
                 Set oRecordset = Nothing
              End Function
              \Rems

              ~
              Similar Post:
              import data from from Text-File containing tab-separated-values
              ~
              Last edited by Rems; 31st January 2010, 20:17.

              This posting is provided "AS IS" with no warranties, and confers no rights.

              __________________

              ** Remember to give credit where credit's due **
              and leave Reputation Points for meaningful posts

              Comment

              Working...
              X