Coverting from Excel to LDIF format

[parkermc]

Hi ,

I am very very new to VB and don't know how to proceed with coding this.

I have a file in excel in the format below

Name....................UID...................Password
Marie Lily...............myoune.................9eW2n+st
Rita Jain..................rjain....................yE#eCA2a
Peter Craig.............pcraig.....................Qutru4r?

and I need to convert this data/excel sheet into the format below:

dn: cn=myoune,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Marie
sn: Lily
uid: myoune
userPassword: 9eW2n+st

dn: cn=rjain,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Rita
sn: Jain
uid: rjain
userPassword: yE#eCA2a

dn: cn=pcraig,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Peter
sn: Craig
uid: pcraig
userPassword: Qutru4r?

I have some 2,000 records as such to do the conversion and it is very tiresome to manually create this file. Is there any way to expedite this process using a VB.NET program to create the file? Truthfully, the file being created can be a txt file and the extension changed to LDIF before importing. I'm using VB.NET 2005 and have the whole Visual Studio 2005 installed.

I would really appreciate if anyone can help.

Thank you.

[parkermc]

Dont' know if this ever was resolved, but here's an option. Create a button object in an Excel worksheet and create the macros below. When ran, this will create a .txt file as you requested where the txt extension can be changed to .ldif for importing.

You will need 4 columns in the Excel sheet.... Fname, Lname, UID, Password

Sub Button2_Click()
  Call WriteIt
End Sub

Public Sub WriteIt()
  WriteFile Range("A2", Cells(Rows.Count, 1).End(xlUp).Address), _
  ThisWorkbook.Path & "\Profile_Upload.txt"
End Sub

Public Sub WriteFile(fromRange As Range, toFile As String)
  Dim iHandle As Integer, cell As Range
  iHandle = FreeFile
  Open toFile For Output Access Write As #iHandle
  For Each cell In fromRange
    Print #iHandle, "dn: cn=" & cell.Offset(0, 2).Value & ",cn=Users,ou=MyDomain,dc=com"
    Print #iHandle, "objectclass: inetOrgPerson"
    Print #iHandle, "objectclass: organizationalPerson"
    Print #iHandle, "objectclass: person"
    Print #iHandle, "objectclass: top"
    Print #iHandle, "givenName: " & cell.Value
    Print #iHandle, "sn: " & cell.Offset(0, 1).Value
    Print #iHandle, "uid: " & cell.Offset(0, 2).Value
    Print #iHandle, "userPassword: " & cell.Offset(0, 3).Value
    Print #iHandle,

  Next cell
  Close #iHandle
End Sub

[mltytskr]

Hey parkermc, I'm trying your code for a macro, but I keep getting a runtime error on the public sub writeit. I'm using Excel 2007.

Sub Button2_Click()
  Call WriteIt
End Sub

Public Sub WriteIt()
  WriteFile Range("A2", Cells(Rows.Count, 1).End(x1Up).Address), _
  ThisWorkbook.Path & "\Profile_Upload.txt"
End Sub

Public Sub WriteFile(fromRange As Range, toFile As String)
  Dim iHandle As Integer, cell As Range
  iHandle = FreeFile
  Open toFile For Output Access Write As #iHandle
  For Each cell In fromRange
    Print #iHandle, "dn: cn=" & cell.Offset(0, 12).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "changetype: add"
    Print #iHandle, "objectclass: inetOrgPerson"
    Print #iHandle, "objectclass: organizationalPerson"
    Print #iHandle, "objectclass: person"
    Print #iHandle, "objectclass: top"
    Print #iHandle, "givenName: " & cell.Offset(0, 3).Value
    Print #iHandle, "sn: " & cell.Offset(0, 2).Value
    Print #iHandle, "initials: " & cell.Offset(0, 6).Value
    Print #iHandle, "fullName: " & cell.Offset(0, 10).Value
    Print #iHandle, "uid: " & cell.Offset(0, 12).Value
    Print #iHandle, "securityEquals: cn=" & cell.Offset(0, 1).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "groupMembership: cn=" & cell.Offset(0, 1).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "passwordRequired: TRUE"
    Print #iHandle, "passwordAllowChange: TRUE"
    Print #iHandle, "userPassword: changeME10"
    Print #iHandle,
    Print #iHandle, "dn: cn=" & cell.Offset(0, 1).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "changetype: modify"
    Print #iHandle, "add: member"
    Print #iHandle, "member: cn=" & cell.Offset(0, 12).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "-"
    Print #iHandle, "add: equivalentToMe"
    Print #iHandle, "equivalentToMe: cn=" & cell.Offset(0, 12).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle,

  Next cell
  Close #iHandle
End Sub

[mltytskr]

OK, I was able to write a script that does almost what I want. The only thing I need help changing is the Print... initials:... line. I want to skip this output if the cell is empty. Right now if the cell is empty it prints (in the LDIF file) initials: , but Novell will not accept that. I'm aware I can sort the data by that field so that the empty ones would be together then edit the output file, but I want to avoid that if it can be scripted to do what I want. Here is the updated working code for me:

Sub WriteFile()
  Dim x As Integer, toFile As String, iHandle As Integer
  iHandle = FreeFile
  NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
  Range("A2").Select
  toFile = ThisWorkbook.Path & "\Profile_Upload.txt"
  Open toFile For Output Access Write As #iHandle
  For x = 2 To NumRows + 1
    Print #iHandle, "dn: cn=" & Cells(x, 10).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "changetype: add"
    Print #iHandle, "objectclass: inetOrgPerson"
    Print #iHandle, "objectclass: organizationalPerson"
    Print #iHandle, "objectclass: person"
    Print #iHandle, "objectclass: top"
    Print #iHandle, "givenName: " & Cells(x, 4).Value
    Print #iHandle, "sn: " & Cells(x, 3).Value
    Print #iHandle, "initials: " & Cells(x, 6).Value
    Print #iHandle, "fullName: " & Cells(x, 8).Value
    Print #iHandle, "userPassword: changeME10"
    Print #iHandle, "securityEquals: cn=" & Cells(x, 2).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "groupMembership: cn=" & Cells(x, 2).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "passwordRequired: TRUE"
    Print #iHandle, "passwordAllowChange: TRUE"
    Print #iHandle,
    Print #iHandle, "dn: cn=" & Cells(x, 2).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "changetype: modify"
    Print #iHandle, "add: equivalentToMe"
    Print #iHandle, "equivalentToMe: cn=" & Cells(x, 10).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle, "-"
    Print #iHandle, "add: member"
    Print #iHandle, "member: cn=" & Cells(x, 10).Value & ",ou=students,ou=home,o=cucps"
    Print #iHandle,
    ActiveCell.Offset(1, 0).Select
  Next
  Close #iHandle
End Sub

[mltytskr]

OK, I figured out that problem too. So basically, this script will create a text file in LDIF format and add the user to a group that is defined in column B. I also have the spreadsheet set up to check for duplicates and to change the username to add the middle initial. PM if you would like more information or details. I can only help you with what I have created as I knew nothing about scripting macros before this.

Print #iHandle, "sn: " & Cells(x, 3).Value
    If Not Cells(x, 6) = "" Then
    Print #iHandle, "initials: " & Cells(x, 6).Value
    End If
    Print #iHandle, "fullName: " & Cells(x, 8).Value