Create excel data to LDIF format using PowerShell

Question

My Requirement is like, we will ask requester to fill an excel sheet where they will put the values in column uiddn(A1 in excel sheet) and nIds(B1 in excel sheet).

For Example:

uiddn nIds

uid=uid12345,cn=abc,cn=def,dc=xyx 7_54321

uid=uid6789,cn=abc,cn=def,dc=xyx 5_9876

Once those details are filled, a PowerShell script should take the values and create a ldif in the name 'removenid.ldif' and the ldif should content as shown below:

dn: uid=uid12345,cn=abc,cn=def,dc=xyx

changetype: modify

delete: nIds

nIds: 7_54321

-

dn: uid=uid6789,cn=abc,cn=def,dc=xyx

changetype: modify

delete: nIds

nIds: 5_9876

Along with it should create another ldif file in the name 'groupremoval.ldif' where the ldif file content should be:

dn: cn=ghk,cn=klm,cn=opu,dc=xyx

changetype: modify

delete: member

member: uid=uid12345,cn=abc,cn=def,dc=xyx---only this value it should take from the excel sheet

-

dn: cn=ghk,cn=klm,cn=opu,dc=xyx

changetype: modify

delete: member

member: uid=uid6789,cn=abc,cn=def,dc=xyx---only this value it should take from the excel sheet

Please help me with the PowerShell script.

Answer 1

Hi,

Although you can read Excel files, if possible I'd suggest asking for the info as a CSV or saving to CSV yourself as its a bit easier to work with. With the CSV in the following format:

uiddn,nids

"uid=uid12345,cn=abc,cn=def,dc=xyx",7_54321

"uid=uid6789,cn=abc,cn=def,dc=xyx",5_9876

This code should get you fairly close to what your after if my understanding of your request is right. I've done something very similar to create config files to create new users on a firewall for VPN accounts:

$InputCSV = "C:\uiddn.csv"
$Output_Folder = "C:\"

Import-csv -path $InputCSV | ForEach {
	"dn: $($_.uiddn)" | Out-File -FilePath "$($Output_Folder)removenid.ldif" -Append
	"changetype: modify" | Out-File -FilePath "$($Output_Folder)removenid.ldif" -Append
	"delete: nIds" | Out-File -FilePath "$($Output_Folder)removenid.ldif" -Append
	"nIds: $($_.nids)" | Out-File -FilePath "$($Output_Folder)removenid.ldif" -Append
	"-" | Out-File -FilePath "$($Output_Folder)removenid.ldif" -Append
	
	"n: cn=ghk,cn=klm,cn=opu,dc=xyx" | Out-File -FilePath "$($Output_Folder)groupremoval.ldif" -Append
	"changetype: modify" | Out-File -FilePath "$($Output_Folder)groupremoval.ldif" -Append
	"delete: member" | Out-File -FilePath "$($Output_Folder)groupremoval.ldif" -Append
	"member: $($_.uiddn)" | Out-File -FilePath "$($Output_Folder)groupremoval.ldif" -Append
	"-" | Out-File -FilePath "$($Output_Folder)groupremoval.ldif" -Append
}

The lines with "-" can be changed to just "" if you want a blank line - I'm not familiar with the specifics of the ldif format so I'm not sure if a trailing - in the file causes issues or not.

Answer 2

Although this looks like more work than the answer given by "DaveK" it's easier to extend the code (i.e., use a template) to use for other data sets. It also uses the ImportExcel PowerShell module to avoid having to use an intermediate step of exporting the Excel file to a CSV.

$IDFile = "c:\junk\removenid.ldif"
$GROUPFile = "c:\junk\groupremoval.ldif"
$ExcelFile = "C:\junk\D.xlsx"

# for changetype delete nlds
$n = [ordered]@{
    dn = ""
    changetype = "modify"
    delete = "nlds"
    nlds = ""
}
$m = [ordered]@{
    dn = "cn=ghk,cn=klm,cn=opu,dc=xyx"
    changetype = "modify"
    delete = "member"
    member = ""
}
# Remove files if they exist
$IDFile,$GroupFile|
    ForEach-Object{
        if (Test-Path -Path $_ -PathType Leaf){
            Remove-Item $_
        }
    }
Import-Excel -Path $ExcelFile| 
    ForEach-Object {
        $ul = @()
        $n.dn = $_.uiddn
        $n.nlds = $_.nlds
        $n.GetEnumerator()|
            ForEach-Object{
                $ul += "{0}: {1}" -f $_.Key,$_.Value
            }
        $ul += "-"  # end of property list for 'delete'
        $ul += ""   # empty line ends this object
        Out-File -InputObject $ul -FilePath $IDFile -Append
        
        $ul = @()
        $m.member = $_.uiddn
        $m.GetEnumerator()|
            ForEach-Object{
                $ul += "{0}: {1}" -f $_.Key,$_.Value
            }
        $ul += "-"  # end of property list for 'modify'
        $ul += ""   # empty line ends this object
        Out-File -InputObject $ul -FilePath $GROUPFile -Append
    }

Answer 3

Hi. Thank you for your question and reaching out. I’d be more than happy to help you with your query.

Creating an LDIF file from Excel data is a fairly straightforward process if you have the right tools. You can use PowerShell, a scripting language and command line shell, to accomplish this task.

First, open Excel and find the spreadsheet containing the data you want to convert into the LDIF format. Make sure the data is clean and organized, as this will make the conversion process easier.

Next, open PowerShell and type in the command “Export-CSV -Path [File Path] -Delimiter "`t" -NoTypeInformation”. Replace “[File Path]” with the file path of the Excel spreadsheet. This command will convert the Excel file into a comma-delimited CSV file.

Now, you need to convert the CSV file into LDIF format. To do this, type in the command “Import-LDIF –Path [File Path] -Encoding Default”. Replace “[File Path]” with the file path of the CSV file. This command will convert the CSV file into a LDIF file.

You have successfully converted an Excel file into a LDIF file using PowerShell. To make sure that the data was converted correctly, open the LDIF file and check the data. If everything is correct, the conversion process is complete.

If the reply was helpful, please don’t forget to upvote or accept as answer, thank you.