comment on

I have some code here that I will be glad to share with you. I am not going to do much to clean it up but it should be relatively readable. I use this code to connect to my Exchange Server and read all of my contacts. I then loop through each contact and store it into a table in a MySQL DB where the update date in the table is older than the update date on the contact.

You will not need the MySQL code and that portion can just be omitted. The code to connect to Outlook and get the properties is pretty easy to sort out and find.

Let me know if you have any questions about this code or what it is doing and I will do my best to answer.

Here is the code. Hope it helps.

#!/usr/bin/perl -w

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Outlook';
use Date::Manip;
use DBI();

&Date_Init("TZ=CST6CDT");

my $argcount = 0;
my $arg;
my $do_db;
my $do_count = 0;
my $tc = 0;
my $uc = 0;
my $ic = 0;

while (@ARGV) {
    $arg = shift(@ARGV);
    if ($arg =~ /-todb/) {
        $do_db = 1;
    } elsif ($arg =~ /-count/) {
        $do_count = 1;
    }
}

# mysql connection variables
my $dbhost = '1.2.3.4';  # you will need to set this
my $dbuser = 'pass';  # you will need to set this
my $dbpass = 'pass';  #you will need to set this
my $dbdb = 'db';  #you will need to set this
my $dsn = "DBI:mysqlPP:database=$dbdb;host=$dbhost";
my $dbh;

if ($do_db) {
print "Connecting to DB ...\n";
print "dsn: $dsn\n";
$dbh = DBI->connect($dsn,$dbuser,$dbpass) or die "couldn't connect to 
+$dbhost: " . DBI->errstr . "\n";
}

my @PhoneProperties = (
  "Account",
  "Anniversary",
  "AssistantName",
  "AssistantTelephoneNumber",
  "BillingInformation",
  "Birthday",
  "Body",
  "Business2TelephoneNumber",
  "BusinessAddress",
  "BusinessAddressCity",
  "BusinessAddressCountry",
  "BusinessAddressPostOfficeBox",
  "BusinessAddressPostalCode",
  "BusinessAddressState",
  "BusinessAddressStreet",
  "BusinessFaxNumber",
  "BusinessHomePage",
  "BusinessTelephoneNumber",
  "CallbackTelephoneNumber",
  "Categories",
  "CarTelephoneNumber",
  "Children",
  "Companies",
  "CompanyLastFirstNoSpace",
  "CompanyLastFirstSpaceOnly",
  "CompanyMainTelephoneNumber",
  "CompanyName",
  "ComputerNetworkName",
  "ConversationIndex",
  "ConversationTopic",
  "CreationTime",
  "CustomerID",
  "Department",
  "Email1Address",
  "Email1AddressType",
  "Email1DisplayName",
  "Email1EntryID",
  "Email2Address",
  "Email2AddressType",
  "Email2DisplayName",
  "Email2EntryID",
  "Email3Address",
  "Email3AddressType",
  "Email3DisplayName",
  "Email3EntryID",
  "EntryID",
  "FTPSite",
  "FileAs",
  "FirstName",
  "FullName",
  "FullNameAndCompany",
  "GovernmentIDNumber",
  "Hobby",
  "Home2TelephoneNumber",
  "HomeAddress",
  "HomeAddressCity",
  "HomeAddressCountry",
  "HomeAddressPostOfficeBox",
  "HomeAddressPostalCode",
  "HomeAddressState",
  "HomeAddressStreet",
  "HomeFaxNumber",
  "HomeTelephoneNumber",
  "IMAddress",
  "ISDNNumber",
  "Initials",
  "InternetFreeBusyAddress",
  "JobTitle",
  "LastFirstAndSuffix",
  "LastFirstNoSpace",
  "LastFirstNoSpaceAndSuffix",
  "LastFirstNoSpaceCompany",
  "LastFirstSpaceOnly",
  "LastFirstSpaceOnlyCompany",
  "LastModificationTime",
  "LastName",
  "LastNameAndFirstName",
  "MailingAddress",
  "MailingAddressCity",
  "MailingAddressCountry",
  "MailingAddressPostOfficeBox",
  "MailingAddressPostalCode",
  "MailingAddressState",
  "MailingAddressStreet",
  "ManagerName",
  "MessageClass",
  "MiddleName",
  "Mileage",
  "MobileTelephoneNumber",
  "NetMeetingAlias",
  "NetMeetingServer",
  "NickName",
  "OfficeLocation",
  "OrganizationalIDNumber",
  "OtherAddress",
  "OtherAddressCity",
  "OtherAddressCountry",
  "OtherAddressPostOfficeBox",
  "OtherAddressPostalCode",
  "OtherAddressState",
  "OtherAddressStreet",
  "OtherFaxNumber",
  "OtherTelephoneNumber",
  "PagerNumber",
  "PersonalHomePage",
  "PrimaryTelephoneNumber",
  "Profession",
  "RadioTelephoneNumber",
  "ReferredBy",
  "Spouse",
  "Subject",
  "Suffix",
  "TTYTDDTelephoneNumber",
  "TelexNumber",
  "Title",
  "User1",
  "User2",
  "User3",
  "User4",
  "UserCertificate",
  "WebPage",
  "YomiCompanyName",
  "YomiFirstName",
  "YomiLastName"
);

print "Connecting to Outlook ...\n";
my $outlook;
$outlook = Win32::OLE->new('Outlook.Application');

my $namespace = $outlook->GetNamespace("MAPI");
my $Folder = $namespace->GetDefaultFolder(olFolderContacts);

print $Folder->Name,"\n";

my $items = $Folder->Items;
print $items->Count,"\n";

#my $activeexplorer;
#$activeexplorer = $outlook->ActiveExplorer;

#print $activeexplorer->Caption,"\n";

#my $items = $activeexplorer->CurrentFolder->Items;
#print $items->Count,"\n";

my $linenumber = 1;
print "CR;NR;PROPERTY;DATA\n";

my $Kontakt;
my $PhoneProp;
my $ItemIndex = 1;
my $contactno = 0;
my $v;
my %allprops;
my $db_mod_date;
my $ex_mod_date;
my $exdt;
my $dbdt;
my $flag;

#my $query_find_id = "select * from OutlookContact where EntryID = ?";
my $query_find_id = "select count(*) from OutlookContact where EntryID
+ = ?";
my $query_get_mod_dt = "select LastModificationTime from OutlookContac
+t where EntryID = ?";
my $query_ins = "insert into OutlookContact (EntryID) values (?)";
my $sth_fi;
my $sth_ins;
my $sth_gmd;

if ($do_db) {
    print "preparing statements...\n";
    $sth_fi = $dbh->prepare($query_find_id);
    $sth_ins = $dbh->prepare($query_ins);
    $sth_gmd = $dbh->prepare($query_get_mod_dt);

    if (!defined $sth_fi) {
        print "sth_fi undefined\n";
    }
    if (!defined $sth_ins) {
        print "sth_ins undefined\n";
    }
    if (!defined $sth_gmd) {
        print "sth_ins undefined\n";
    }
}

print "Loop through items ...\n";
while ($ItemIndex <= $items->Count) {
  $Kontakt = $items->item($ItemIndex);
  $contactno++;
  $tc++;

  my $entryID = $Kontakt->{"EntryID"};
  print "entryID: $entryID\n";
  my $ex_mod_date = $Kontakt->{"LastModificationTime"};
  my @junk = split(/\s+/, $ex_mod_date);
  $exdt = ParseDate($junk[0]);
  print "EX LastModificationTime: $ex_mod_date\n";

if ($do_db) {
  print "checking for existing Entry ID...\n";
  $sth_fi->execute($entryID);

  my @rowdata = $sth_fi->fetchrow_array();
  my $rc = $rowdata[0];

  print "EntryID rows found: ".$rc."\n";
  if ($rc == 0) {
    print "EntryID does not exist, inserting...\n";
    $sth_ins->execute($entryID);
    $ic++;
  } else {
    $sth_gmd->execute($entryID);
    my @rows = $sth_gmd->fetchrow_array();
    $db_mod_date = $rows[0];
    $dbdt = ParseDate($db_mod_date);
    print "DB LastModificationTime: $db_mod_date\n";
  }
}

$flag = Date_Cmp($dbdt, $exdt);
print "Date_Cmp flag: $flag\n";
if ($flag < 0) {
  foreach $PhoneProp (@PhoneProperties) {
    my $prop = $Kontakt->{"$PhoneProp"};
    if ($prop) {
      # save the property to get a complete list of used properties
      if ($PhoneProp =~ /Time/) {
        $v = Variant(VT_DATE, $prop);
    #$prop = $v->Date("yyyy-mm-dd") . " " . $v-Time(TIME_FORCE24HOURFO
+RMAT|TIME_NOTIMEMARKER);
        $prop = $v->Date("yyyy-MM-dd");
      }
      if ($allprops{$PhoneProp}) { 
        if ($allprops{$PhoneProp} < length($prop)) {
          $allprops{$PhoneProp} = length($prop);
    }
      } else {
        $allprops{$PhoneProp} = length($prop);
      }

if ($do_db) {  
    my $insprop = $dbh->quote($prop);
      my $query_upd = "update OutlookContact set $PhoneProp = $insprop
+ where EntryID = '$entryID'";
      print "Query: $query_upd\n";
      $dbh->do($query_upd);
      $uc++;
}
      
      print $contactno, ";", $linenumber++, ";", $PhoneProp, ";", $pro
+p, "\n";
      #print "$number=", substr( $Kontakt->FullName, 1, 15 ),"\n";
    }
  }

  }
 $ItemIndex++;
}

my $key;

print "Properties seen:\n";
foreach $key (keys %allprops) {
    if ($key =~ /Time/) {
        print $key." date,\n";
    } else {
        print $key." varchar(".$allprops{$key}."),\n";
    }
}

print "Total records: $tc \n";
print "Updated records: $uc \n";
print "Inserted records: $ic \n";
[download]

In reply to Re: Perl and Exchange/Windows address books by bfdi533
in thread Perl and Exchange/Windows address books by mhacker

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.