A perl script to convert from Excel to LDIF

Overview

We have developed a simple solution which allows for the conversion of a Microsoft Excel file to an LDIF file. The resulting LDIF file can then be easily imported into Novell eDirectory. 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
  • Smart Date conversion for columns ending with ‘Date’
  • Smart substring function can be applied to any column value

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) (link is external) and ActivePerl (cross platform) (link is external).
Once perl is installed, you must download and install the modules listed below. Please install the modules in the order they are listed. An alternative method is to download a version of Strawberry Perl which has the modules already installed here(link is external).

Excel2ldif.pl

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

ignore.txt

This is a comma separated file containing the attribute names which should 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/2012)
  • 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
This program has been tested with Microsoft Excel 2007 and 2010

Download the program

Hosted on Belkast OwnCloud