Page 1 of 1

Outlook Import

Posted: Wed Oct 12, 2005 8:57 pm
by fdesensi
I am having trouble with a script that Imports data from Microsoft Outllok 2k+/XP and Outlook Express

The issue I have is that Outlook allows you to enter characters into the fields such as , " "
The comma is the default delimeter and each string is inclosed in double quotes.

So if the user just enters alpha-numeric data, my script works well, but if the enter something
like John, "The Hammer", Smith that is going to fudge my script.

Finally, the server I am on does not support instalation of new pear packages, so
I can't use the pear package which does this.

If anyone has worked on something similar and has a script I could use, or if there is
a tutorial I would greatly appreciate it.


Here is an example of a Outlook 2000 dump

Code: Select all

"Title","First Name","Middle Name","Last Name","Suffix","Company","Department","Job Title","Business Street","Business Street 2","Business Street 3","Business City","Business State","Business Postal Code","Business Country","Home Street","Home Street 2","Home Street 3","Home City","Home State","Home Postal Code","Home Country","Other Street","Other Street 2","Other Street 3","Other City","Other State","Other Postal Code","Other Country","Assistant's Phone","Business Fax","Business Phone","Business Phone 2","Callback","Car Phone","Company Main Phone","Home Fax","Home Phone","Home Phone 2","ISDN","Mobile Phone","Other Fax","Other Phone","Pager","Primary Phone","Radio Phone","TTY/TDD Phone","Telex","Account","Anniversary","Assistant's Name","Billing Information","Birthday","Business Address PO Box","Categories","Children","Directory Server","E-mail Address","E-mail Type","E-mail Display Name","E-mail 2 Address","E-mail 2 Type","E-mail 2 Display Name","E-mail 3 Address","E-mail 3 Type","E-mail 3 Display Name","Gender","Government ID Number","Hobby","Home Address PO Box","Initials","Internet Free Busy","Keywords","Language","Location","Manager's Name","Mileage","Notes","Office Location","Organizational ID Number","Other Address PO Box","Priority","Private","Profession","Referred By","Sensitivity","Spouse","User 1","User 2","User 3","User 4","Web Page"
"","Francesco","A.","DeSensi","","Ultima Designs","","CTO",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"","0/0/00",,,"0/0/00",,,,,"fdesensi@ultimadesigns.com","SMTP","DeSensi, Francesco A. (fdesensi@ultimadesigns.com)",,,,,,,"Unspecified",,,,"F.A.D.",,"","","",,,,,,,"Normal","False",,,"Normal"
And this is my script so far if you want to see it

Code: Select all

<?php
	include("../../inc/CodeLibrary/FileSystem/filecommands.php");

	/**************************************************************************************
	*
	* Title:  	OUTLOOK_IMPORT
	* Version: 	1.0
	* Author: 	Francesco DeSensi
	*
	* Version Notes:
	*	See file /docs/history.txt for further information
	*		
	*		Field Map
	*		----------------------------------------------------------
	*		Title						=> Title
	*		First Name					=> FirstName
	*		Middle Name					=> MI
	*		Last Name					=> LastName
	*		Suffix						=> Suffix
	*		Company						=> Company
	*		Department					=> Department
	*		Job Title					=> JobTitle
	*		Business Street				=> BusinessAddress1
	*		Business Street 2			=> BusinessAddress2
	*		Business Street 3			
	*		Business City				=> BusinessCity
	*		Business State				=> BusinessState
	*		Business Postal Code		=> BusinessZipCode
	*		Business Country			
	* 		Home Street					=> HomeAddress1
	*		Home Street 2				=> HomeAddress2
	*		Home Street 3
	*		Home City					=> HomeCity
	*		Home State					=> HomeState
	*		Home Postal Code			=> HomeZipCode
	*		Home Country
	*		Other Street
	*		Other Street 2
	*		Other Street 3
	*		Other City
	*		Other State
	*		Other Postal Code
	*		Other Country
	*		Assistant's Phone
	*		Business Fax				=> WorkFax
	*		Business Phone				=> WorkPhone 
	*		Business Phone 2	
	*		Callback
	*		Car Phone					=> CellPhone ( This could also be Mobile Phone )
	*		Company Main Phone
	*		Home Fax
	*		Home Phone					=> HomePhone
	*		Home Phone 2
	*		ISDN
	*		Mobile Phone				=> CellPhone ( This could also be carphone )
	*		Other Fax
	*		Other Phone
	*		Pager
	*		Primary Phone
	*		Radio Phone
	*		TTY/TDD Phone
	*		Telex
	*		Account
	*		Anniversary
	*		Assistant's Name
	*		Billing Information
	*		Birthday
	*		Business Address PO Box
	*		Categories
	*		Children
	*		Directory Server
	*		E-mail Address				=> EmailAddress
	*		E-mail Type
	*		E-mail Display Name
	*		E-mail 2 Address			=> EmailAddress2
	*		E-mail 2 Type
	*		E-mail 2 Display Name
	*		E-mail 3 Address			=> EmailAddress3
	*		E-mail 3 Type
	*		E-mail 3 Display Name
	*		Gender
	*		Government ID Number
	*		Hobby
	*		Home Address PO Box
	*		Initials
	*		Internet Free Busy
	*		Keywords
	*		Language
	*		Location
	*		Manager's Name
	*		Mileage
	*		Notes
	*		Office Location
	*		Organizational ID Number
	*		Other Address PO Box
	*		Priority
	*		Private
	*		Profession
	*		Referred By
	*		Sensitivity
	*		Spouse
	*		User 1
	*		User 2
	*		User 3
	*		User 4
	*		Web Page						=> Website
	*		
	**************************************************************************************/	
	class OUTLOOK_IMPORT
	{
		var $source; 
		
		var $userid;
		var $clientid;
		
		var $tmpFile;

		var $aFields;
		var $numFields;
		var $noneField;
		
		var $Title;
		var $FirstNamee;
		var $MI;
		var $LastName;
		var $Suffix;
		var $Company;
		var $Department;
		var $JobTitle;
		var $BusinessAddress1;
		var $BusinessAddress2;
		var $BusinessCity;
		var $BusinessState;
		var $BusinessZipCode;
		var $HomeAddress1;
		var $HomeAddress2;
		var $HomeCity;
		var $HomeState;
		var $HomeZipCode;
		var $WorkFax;
		var $WorkPhone;
		var $CellPhone;
		var $HomePhone;
		var $CellPhone;
		var $Website;
		var $EmailAddress;
		var $EmailAddress2;
		var $EmailAddress3;
		
		var $OutlookExpress;
		
		var $badRows;
		var	$goodRows;
		var $totalRows;
		
		var $badRecords;
		
		
		/****************************************************************
		* OUTLOOK_IMPORT()
		*
		* Return:
		* 	N/A
		*
		* Desc: 
		*	This is the constructor
		****************************************************************/
		function OUTLOOK_IMPORT()
		{
			$this->source				= $_SESSION[source];
		
			$this->userid				= $_SESSION[userid];
			$this->clientid				= $_SESSION[clientid];
			
			$this->tmpFile 				= tmpStore("tmp/");
			$this->aFields;
			$this->numFields;
			$this->noneField;
			
			$this->Title;
			$this->FirstNamee;
			$this->MI;
			$this->LastName;
			$this->Suffix;
			$this->Company;
			$this->Department;
			$this->JobTitle;
			$this->BusinessAddress1;
			$this->BusinessAddress2;
			$this->BusinessCity;
			$this->BusinessState;
			$this->BusinessZipCode;
			$this->HomeAddress1;
			$this->HomeAddress2;
			$this->HomeCity;
			$this->HomeState;
			$this->HomeZipCode;
			$this->WorkFax;
			$this->WorkPhone;
			$this->CellPhone;
			$this->HomePhone;
			$this->CellPhone;
			$this->Website;
			
			$this->OutlookExpress;
			
			$this->badRows;
			$this->goodRows;		
			$this->TotalRows;	
			
			$this->badRecords; 
		}
	
	
		/****************************************************************
		* IMPORT()
		*
		* Return:
		* 	N/A
		*
		* Desc: 
		*	Bring together all of the other methods in order
		*	to import the csv values into the CONTACT table
		*   in the database.
		****************************************************************/
		function Import()
		{
			// Get the file that we are working with
			$this->aFields 		= $this->DefineFields($this->tmpFile);
			
			// Determine the numer of fields
			$this->numFields 	= count($this->aFields);
					
			// Assign an array location for when there 
			// is nothing in a field
			$this->noneField	= $this->numFields + 1;
			
			// Map array locations for each field
			$this->MapFields($this->aFields);
			
			$file	= file($this->tmpFile);
			
			foreach($file as $line_num => $line)
			{
				if($line_num != 0)	// Skip the first 'field' line
				{

					// Do it twice, the second time, because there is at least one set left behind
					// after the first application in most cases
					$line		= preg_replace("/,(,){1}/", ",\"\",", $line);
					$line		= preg_replace("/,(,){1}/", ",\"\",", $line);
					
					$pattern		= "/\",\"/";
					
					$values		= preg_split($pattern, $line);
					$max		= count($values) -1;
					
					$values[0]		= str_replace("\"","", $values[0]);
					$values[0]		= str_replace(",","", $values[0]);
					$values[$max]	= str_replace("\"","", $values[$max]);
					$values[$max]	= str_replace(",","", $values[$max]);

					
					print($values[$this->FirstName]);
					
					// Replace parenthesis in phone numbers so that they work with CIRA
					$values[$this->HomePhone] = str_replace("(","",$values[$this->HomePhone]);
					$values[$this->HomePhone] = str_replace(") ","-",$values[$this->HomePhone]);
					$values[$this->WorkPhone] = str_replace("(","",$values[$this->WorkPhone]);
					$values[$this->WorkPhone] = str_replace(") ","-",$values[$this->WorkPhone]);
					$values[$this->WorkFax] = str_replace("(","",$values[$this->WorkFax]);
					$values[$this->WorkFax] = str_replace(") ","-",$values[$this->WorkFax]);
					$values[$this->CellPhone] = str_replace("(","",$values[$this->CellPhone]);
					$values[$this->CellPhone] = str_replace(") ","-",$values[$this->CellPhone]);
					
					// Fields that were not found in the csv are set to blank
					$values[$this->noneField] = "";
					
					$sql	= mysql_query("INSERT INTO CONTACT  ( `Title` , `FirstName` , `MI` , `LastName` , `Suffix` , `JobTitle` , `Company` , `Website` , `AIM` , `MSN` , `Yahoo` , `Picture` , `HomePhone` , `WorkPhone` , `WorkFax` , `CellPhone` , `BusinessAddress1` , `BusinessAddress2` , `BusinessCity` , `BusinessState` , `BusinessZipCode` , `HomeAddress1` , `HomeAddress2` , `HomeCity` , `HomeState` , `HomeZipCode` , `Department` , `EmailAddress` , `EmailAddress2` , `EmailAddress3` , `ClientName` , `Owner` ) " .
					"VALUES('" .  $values[$this->Title] . "','" . 
					$values[$this->FirstName] ."','" . 
					$values[$this->MI] ."','" . 
					$values[$this->LastName] ."','" .
					$values[$this->Suffix] ."','" . 
					$values[$this->JobTitle] ."','" .
					$values[$this->Company] ."','" . 
					$values[$this->Website] ."','','','','','" . 
					$values[$this->HomePhone] ."','" . 
					$values[$this->WorkPhone] ."','" . 
					$values[$this->WorkFax] ."','" . 
					$values[$this->CellPhone] ."','" . 
					$values[$this->BusinessAddress1] ."','" . 
					$values[$this->BusinessAddress2] . "','" .
					$values[$this->BusinessCity] . "','" . 
					$values[$this->BusinessState] . "','" . 
					$values[$this->BusinessZipCode] ."','" . 
					$values[$this->HomeAddress1] ."','" . 
					$values[$this->HomeAddress2] ."','" . 
					$values[$this->HomeCity] ."','" . 
					$values[$this->HomeState] ."','" . 
					$values[$this->HomeZipCode] ."','" . 
					$values[$this->Department] . "','" . 
					$values[$this->EmailAddress] . "','" . 
					$values[$this->EmailAddress2] . "','" . 
					$values[$this->EmailAddress3] . 
					"','$_SESSION[clientname]','$_SESSION[userid]')") or die(mysql_error());
				}
			}
			
			// Cleanup
			delFile($this->tmpFile);
		}
		
		/*****************************************************************
		* EvaluateImport()
		* 
		* Return:
		*	N/A
		*
		* Desc:
		*	This will evaluate and decide what has actually happened 
		*	during the import, so that we can report to the user 
		*	about failures and what have you.
		*
		*****************************************************************/
		function EvaluateImport()
		{	
			if($this->badRows >= $this->totalRows || $this->goodRows == 0)
			{
				// Import completely failed
			}
			
			if($this->badRows > 0)
			{
				// There are some bad rows
				
				// Display the record number and information 
				// that can be displayed
			}
		}
		
		/*****************************************************************
		* EvaluateRow()
		* 
		* Return:
		*	N/A
		*
		* Desc:
		*	This will evaluate and decide what has actually happened 
		*	during the import, so that we can report to the user 
		*	about failures and what have you.
		*
		*****************************************************************/
		function EvaluateRow($row, $map)
		{	
			// Subtract one for actual value
			$row = $row - 1;
			$map = $map - 1;
		
			// Determine if there was an error with the row
			if(count($row) != count($map))
			{
				$this->badRows++;
			}
			 else
			{
				$this->goodRows++;
			}

			$this->totalRows++;
		}
		
		/****************************************************************
		* MapFields()
		*
		* Return:
		* 	N/A
		*
		* Desc: 
		*	A simple way to allow for a very flexible and accurate
		*	import is to map the fields that we have in our database
		*	to the field locations in the csv. By creating an array
		*	of the fields with DefineFields(), we simple reference
		*	the value for the record by the position in the array.
		*
		*	Example
		*	---------------------
		*
		*	csv = First Name, Last Name, Company
		*		  John, Doe, Ultima Designs
		*
		*
		*	Map = aFields[0] = FirstName (reference) values[0] = John
		*		  
		****************************************************************/
		function MapFields($aFields)
		{
			$this->Title				= array_search("Title", $aFields);
			$this->FirstName			= array_search("First Name", $aFields);
			$this->MI					= array_search("Middle Name", $aFields);
			$this->LastName				= array_search("Last Name", $aFields);
			$this->Suffix				= array_search("Suffix", $aFields);
			$this->Company				= array_search("Company", $aFields);
			$this->Department			= array_search("Department", $aFields);
			$this->JobTitle				= array_search("Job Title", $aFields);
			$this->BusinessAddress1		= array_search("Business Street", $aFields);
			$this->BusinessAddress2		= array_search("Business Street 2", $aFields);
			$this->BusinessCity			= array_search("Business City", $aFields);
			$this->BusinessState		= array_search("Business State", $aFields);
			$this->BusinessZipCode		= array_search("Business Postal Code", $aFields);
			$this->HomeAddress1			= array_search("Home Street", $aFields);
			$this->HomeAddress2			= array_search("Home Street 2", $aFields);
			$this->HomeCity				= array_search("Home City", $aFields);
			$this->HomeState			= array_search("Home State", $aFields);
			$this->HomeZipCode			= array_search("Home Postal Code", $aFields);
			$this->WorkFax				= array_search("Business Fax", $aFields);
			$this->WorkPhone 			= array_search("Business Phone", $aFields);
			$this->CellPhone			= array_search("Mobile Phone", $aFields);
			$this->HomePhone			= array_search("Home Phone", $aFields);
			$this->Website				= array_search("Web Page", $aFields);						// Not being found
			$this->EmailAddress			= array_search("E-mail Address", $aFields);
			$this->EmailAddress2		= array_search("E-mail 2 Address", $aFields);
			$this->EmailAddress3		= array_search("E-mail 3 Address", $aFields);
					
			if(is_numeric($this->Title) == false)
			{
				$this->Title = $this->noneField;
			}
			if(is_numeric($this->FirstName) == false)
			{
				$this->FirstName = $this->noneField;
			}
			if(is_numeric($this->MI) == false)
			{
				$this->MI = $this->noneField;
			}
			if(is_numeric($this->LastName) == false)
			{
				$this->LastName = $this->noneField;
			}
			if(is_numeric($this->Suffix) == false)
			{
				$this->Suffix = $this->noneField;
			}
			if(is_numeric($this->Company) == false)
			{
				$this->Company = $this->noneField;
			}
			if(is_numeric($this->Department) == false)
			{
				$this->Department = $this->noneField;
			}
			if(is_numeric($this->JobTitle) == false)
			{
				$this->JobTitle = $this->noneField;
			}
			if(is_numeric($this->BusinessAddress1) == false)
			{
				$this->BusinessAddress1 = $this->noneField;
			}
			if(is_numeric($this->BusinessAddress2) == false)
			{
				$this->BusinessAddress2 = $this->noneField;
			}
			if(is_numeric($this->BusinessCity) == false)
			{
				$this->BusinessCity = $this->noneField;
			}
			if(is_numeric($this->BusinessState) == false)
			{
				$this->BusinessState = $this->noneField;
			}
			if(is_numeric($this->BusinessZipCode) == false)
			{
				$this->BusinessZipCode = $this->noneField;
			}
			if(is_numeric($this->HomeAddress1) == false)
			{
				$this->HomeAddress1 = $this->noneField;
			}
			if(is_numeric($this->HomeAddress2) == false)
			{
				$this->HomeAddress2 = $this->noneField;
			}
			if(is_numeric($this->HomeCity) == false)
			{
				$this->HomeCity = $this->noneField;
			}
			if(is_numeric($this->HomeState) == false)
			{
				$this->HomeState = $this->noneField;
			}
			if(is_numeric($this->HomeZipCode) == false)
			{
				$this->HomeZipCode = $this->noneField;
			}
			if(is_numeric($this->WorkFax) == false)
			{
				$this->WorkFax = $this->noneField;
			}
			if(is_numeric($this->WorkPhone) == false)
			{
				$this->WorkPhone = $this->noneField;
			}
			if(is_numeric($this->CellPhone) == false)
			{
				$this->CellPhone = $this->noneField;
			}
			if(is_numeric($this->HomePhone) == false)
			{
				$this->HomePhone = $this->noneField;
			}
			if(is_numeric($this->Website) == false)
			{
				$this->Website = $this->noneField;
			}
		}
		
		
		/****************************************************************
		* DefineFields()
		*
		* Return:
		* 	Array
		*
		* Desc: 
		*	This determines the fields that we have been provided with
		*	and provides an array that can be used to map the csv
		*	file.
		****************************************************************/
		function DefineFields($tmpFile)
		{
			// Define the file
			$lines	= file($tmpFile);
			
			// The first line contains the fields
			$fields = $lines[0];
			
			if($OutlookExpress == true)
			{
				$aFields	= explode(",",$fields);
			}
			 else
			{
				// Do it twice, the second time, because there is at least one set left behind
				// after the first application in most cases
				$fields		= preg_replace("/,(,){1}/", ",\"\",", $fields);
				$fields		= preg_replace("/,(,){1}/", ",\"\",", $fields);
				
				$pattern		= "/\",\"/";
				
				$aFields		= preg_split($pattern, $fields);
				$max			= count($aFields) -1;
				
				$aFields[0]		= str_replace("\"","", $aFields[0]);
				$aFields[0]		= str_replace(",","", $aFields[0]);
				$aFields[$max]	= str_replace("\"","", $aFields[$max]);
				$aFields[$max]	= str_replace(",","", $aFields[$max]);
			}
			
			return($aFields);
		}
		
		/****************************************************************
		* CheckCSV()
		*
		* Return:
		* 	Boolean
		*
		* Desc: 
		*	Determine if the file ends in a .csv or .txt extension
		*	before examining it.
		****************************************************************/
		function CheckCSV($tmpFile)
		{
		    if(strstr($tmpFile, ".csv") or strstr($tmpFile, ".CSV"))
			{
				$bValid = true;
			}
			 else
			{
				$bValid = false;
			}
			
			return($bValid);
		}
		
	}
?>

Posted: Thu Oct 13, 2005 12:58 pm
by timvw
Have you had a look at http://www.php.net/fgetcsv? It does most of the work for you...

Posted: Thu Oct 13, 2005 1:14 pm
by fdesensi
I think I am going to cry......

I had no idea that existed.