Belkast Consulting has developed a simple solution that allows one to convert an Microsoft Excel file into an LDIF file. The resulting LDIF file can then be easily imported into any LDAP v3 compliant directory (NetIQ eDirectory, Microsoft Active Directory etc).

Some of the features are:

Written in Perl, so completely cross platform
Reads Excel files in the Microsoft Excel 2007 and 2010 file format
Ignore list functionality provides a means by which only those columns you are interested in are exported
Only those columns that contain data are stored in the output LDIF file
A smart date conversion for columns ending with Date
A smart substring function can be applied to any column value
Works with Microsoft Excel 2007 and 2010

Installation

If a Perl interpreter is not included with your operating system, you must download and install one. Two fine, and reliable, options are Strawberry Perl (for Windows) and ActivePerl.

Once Perl is installed, you must download and install the modules listed below. Please install the modules in the order they are listed.

OLE-Storage_Lite-0.19
Crypt-RC4-2.02
Digest-Perl-MD5-1.8
Spreadsheet-ParseExcel-0.59

excel2ldif

This is the actual Perl file which must be executed in order to convert the XLS file into an LDIF file
ignore.txt

This is a comma separated file containing the attribute names which will not be exported to the LDIF file. This file contains only one line.
trim.conf

This is a tab delimited file containing the attribute name (first value before the tab) and the substring values (second value after the tab). Before the data value assigned to the attribute is written to the LDIF file, the data value is trimmed according to the substring values. This file contains one attribute per line.

Usage

To run the conversion process, use  perl excel2ldif.pl 
You will be prompted for the Excel (XLS, XLSX) file to convert:

  • The Excel file should be in a format where:
    • The first row has column headings (this row is used as the attribute names in the exported LDIF file)
    • The first column in each subsequent row should be the full Distinguished Name (DN) of the User
    • Any field which includes a date should end in ‘Date’ (for example: idamEmploymentDate)
    • The date format is dd/mm/yyyy (for example: 11/12/2023)
  • You will be prompted for the ‘ignore’ file:
    • The ignore file is a comma separated list of fields which will be omitted from the exported LDIF
  • You will be prompted for the ‘trim’ file:
    • The trim file is a tab delimited list comprising the attribute name and the substring parameters, with one attribute per line

The output file will be the name of the input file with the LDIF extension added.
The output file will NOT include those fields which are listed in the ‘ignore’ file.
The output file will NOT include those fields where the value is blank in the imported Excel file.

Download the program
  • Please click here to download the program. The password is ZbqXYQjox4