1 - 2   [2]

PHP COM for Outlook to Access

Also relates to PHP and Basic

I had an urgency earlier today to produce a script to extract details from the body of a number of emails and put the information into an Access database. For any seasoned VB programmer this would seem like a trivial task I am sure, but having become quite accustomed to using the PCRE library to manipulate and extract text, the thought of becoming embroiled in text searching subroutines using VBA's InStr and Mid functions quickly had me concocting alternatives. If only I could get the body of the emails into some PHP arrays and use the well-seasoned preg family to get the information I needed. Time to call on the Windows COM extension once again.

In fact, getting to an email item is considerably less cumbersome than working with Excel via PHP COM. The messages were stored in a subfolder of the Inbox called Enquiries.

$outlook = new COM("Outlook.Application") or die('….');
$ns = $outlook->GetNamespace("MAPI");
$inbox = $ns->GetDefaultFolder(olFolderInbox);
$subfolders = $inbox->Folders;
while ($folder = $subfolders->getNext()) {
   if ($folder->Name == "Enquiries") {

So now $folder references the subfolder Enquiries and I just needed to cycle through each mail item in this folder to grab the body.

$mail = $folder->Items;
while ($item = $mail->getNext()) {  
  $temp = array();
  $temp['body'] = $item->Body;  
  $temp['date'] = date('d/m/y', $item->ReceivedTime);
  $enquiries[] = $temp;

While I was about it, I also grabbed the date each message was received. Conveniently the PHP COM returns this property as a Unix(!) timestamp so it can be fed straight into date() to get the required output.

A few regular expressions later and the data was transformed into an SQL string ready to feed into MS Access. Sticking with the Windows theme, the inbuilt ODBC functions seemed the best bet for this, once Null values had been handled and a System DSN established.

I am in no way promoting this as a method to go about transfering Outlook data to Access. It just demonstrated the flexibility PHP can offer. I needed to do some heavy duty work on the body text to create the field/values I needed and since PHP is the language I use on a daily basis, I found it less time consuming to build this bridge than to dust off and start trawling through old VBA references.

This is one of many areas where Thunderbird shines for me - emails stored in text format ready to be grep'ed.

Posted on Nov 19, 2004 at 03:37:36. [Comments for PHP COM for Outlook to Access- 0]


Also relates to PHP and MySQL

As a keen statistician with a fascination for weather variation over time (in particular in relation to equilibrium tidal variations) I have acquired comprehensive weather data over the past few years from my digital weather station. All the archive pressure and temperature readings are tucked away in Excel spreadsheets - this year I have actually moved over to Open Office as my spreadsheet of choice. I finally decided it was time to move some of this data into a MySQL database on my server, where I could then manipulate the data for display on the web.

To start with I have decided to keep things simple, storing average daily pressure and temperature for graphical display through the GD library. In part for experimentation, I had a look last weekend at the options for achieving the transfer using the PHP Command Shell.

Firstly I tried using ODBC by creating a System DSN for the Microsoft Excel Driver, and SQL to extract the data. The inbuilt ODBC PHP functions are fairly intuitive and the script was quite simple to build. However the solution was too proprietary since I had to first use MS Query to establish the field names for the required columns and synchronising the query itself:

define("COL_DATE", "F2");
define("COL_AVG_PRESSURE", "F27");
define("COL_AVG_TEMP", "F30");


$s_query = "SELECT `" . $arr_month . "$`." . COL_DATE
         . ", `" . $arr_month . "$`." . COL_AVG_TEMP . " "
         . ", `" . $arr_month . "$`." . COL_AVG_PRESSURE . " "
         . "FROM `" . $arr_month . "$` "
         . "ORDER BY `" . $arr_month . "$`." . COL_DATE;

Instead I decided to have a look at the DCOM abilities of PHP. Unfortunately documentation is sparse in the PHP Manual and across the Web generally, with only one clear example of accessing Excel that I could find. So I decided to trial and error a bit to see how much of the Excel Object Model is recognised. Based in part on the example mentioned above this was the best solution I could get to run:

$xls = new COM("Excel.sheet") or die("Did not connect");
print "Application name:{$xls->Application->value}\n";
print "Loaded Version: {$xls->Application->version}\n";
foreach($years as $year)
  $workbook = "C:\weather\_" . $year . ".xls";  
  $wkb = $xls->Application->Workbooks->Open($workbook) 
         or die("Failed to Open Workbook"); 
  $xls->Application->Visible = 1;
  foreach($sheets as $sheet)
    $month = (array_search($sheet, $sheets) + 1);
    $ws = $wkb->Worksheets($sheet);
    for ($day = 1, $i = First_Cell; 
         $i <= Last_Cell; $i++, $day++)
      if (checkdate($month, $day, $year))
        $arr_day[0] = $year . "-" 
                    . $month . "-" . $day;
        $cell = $ws->Cells($i, AD);
        $arr_day[1] = (! empty($cell->value)) 
                    ? sprintf("%01.1f", $cell->value) 
                    : NULL;  
        $cell = $ws->Cells($i, AA);
        $arr_day[2] = (! empty($cell->value)) 
                    ? round($cell->value) 
                    : NULL;          
        $values[] = $arr_day;

This algorithm quite literally steps through each required cell in turn, by cycling through the monthly worksheets and yearly files (note the constants AA and AD define the indexes of the required columns). It is however quite inefficient, and a faster method would be to extract values as range objects using a call like $xls->Application->Range('Jan!$AA$6:$AA$36).Value, but the PHP COM library does not seem to accept this. Still, the above code works and could be manipulated and applied to future data. However, I will probably look to use Python in the future, with a broader implementation of COM via the Win32Com module and its integration with Open Office. At least I now know that MS Office documents can be manipulated by PHP.

Posted on Feb 12, 2004 at 21:26:53. [Comments for Excel To MySQL Via PHP ODBC And COM- 10]

Breadcrumbs Trail

[ Home ] -> TW Blog -> COM
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.030 seconds