Monday, May 07, 2007

Update Active Directory using Perl

Over the weekend, I received a phone call from my manager because there was an emergency to help out in a site migration that was happening somewhere in Texas. One of the things that was needed to be done is to import data from an Excel spreadsheet to a new active directory setup. Well, this is a pretty easy thing to do with perl.

Here's my solution:
#Program to import data to Active Directory
#by: Sam Dela Cruz
#date: 05/04/2007

use Win32;
use Win32::OLE qw(in);
use Win32::OLE::Enum;
use Spreadsheet::ParseExcel::Simple;

#list should be in the form of

my $data_source;
unless (@ARGV) {
    print "Enter input file: ";
    chomp ($data_source = );
else {
    $data_source = shift @ARGV;

my $xls = Spreadsheet::ParseExcel::Simple->read($data_source)
    or die "Cannot read $data_source: $!\n";

foreach my $sheet ($xls->sheets()){
    while ($sheet->has_data){
        my ($usb,
            $fax ) = $sheet->next_row;

        my $user;
        if($user = get_user($usb)){

            #Set Info
                                remove_before_after_space($ln)); #lastname
                                remove_before_after_space($fn)); #firstname
                                remove_before_after_space($mn)); #middlename
                                remove_before_after_space($org)); #organization
                                remove_before_after_space($location)); #location
                                remove_before_after_space($street)); #street
                                remove_before_after_space($city)); #city
                                remove_before_after_space($state)); #state
                                remove_before_after_space($zip)); #zip
                                remove_before_after_space($phone)); #phone
                                remove_before_after_space($mobile)); #mobile
                                remove_before_after_space($fax)); #fax

            unless ($User->SetInfo()){
                print "Uploaded,$usb,$ln,$fn,$mn,$org,$location,$street,$city,$state,$zip,$phone,$mobile,$fax\n";
                print "Cannot upload $usb info: ".Win32::OLE->LastError."\n";
            print "Unable to open $usb\n";

sub get_user {
    my $usb = shift;
    my $user;
    my $ldap = "LDAP://cn=$usb,ou=corporate,ou=users,ou=corporate,dc=acme,dc=acme,dc=com";

    if($user = Win32::OLE->GetObject($ldap)){
        return $user;
    return 0;

sub set_object_property {
    my ($object,$property,$value)=@_;
    if ($value) {
        print "Error: ".Win32::OLE->LastError."," if ($object->Put($property,$value));
    else {
        print "Error: ".Win32::OLE->LastError."," if ($object->PutEx(1,$property,$value));

sub remove_before_after_space {
    my $data = shift;
    $data =~ s/^\s+|\s+$//g;
    return $data;

There is just one gotcha. When setting up object property with the value of NULL or if you want to blank out that property, you will have to use a different method called PutEx with the ADS_PROPERTY_CLEAR = 1, thus:

Instead of