ÿþ <?xml version="1.0" encoding="iso-8859-1"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/NewsLetter.dwt" codeOutsideHTMLIsLocked="true" --> <head> <meta name="keywords" content="data migration,integration,xFusion,ETL,Remote Access,Microsoft Outlook Inbox,CRM / ERP migration," /> <meta name="description" content="Software Labs -Build a clean address list from Outlook Inbox" /> <title>Software Labs-xFusion Dec06 Newsletter</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <style type="text/css" > p.style51{font-family: Arial, sans-serif; font-size:11.8px;color: #34383A } p.style53{font-family: Arial, sans-serif; font-size:11px;color: #0794D8; font-weight:600 } p.style52{font-family: Arial, sans-serif; font-weight: bold; font-size:12px; color: #353539 } </style> </head> <body style="margin-left:0; margin-top:0"> <table width="600" border="1" style="text-align:left; border-color:#CCCCCC" cellpadding="0" cellspacing="0" > <tr> <td style="width:600; height:543" align="left" valign="top"> <table width="600" border="0" style="text-align:left" cellpadding="0" cellspacing="0"> <tr> <td style="height:60" colspan="3" align="left" valign="top"><a href="../../default.aspx"><img src="../NewsLetterHdr.jpg" width="600" height="60" style="border:0" alt=""/></a></td> </tr> <tr> <td style="height:19" colspan="3" align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="3"> <tr style="background-color:#CCCCCC"> <td style="width:77% ; height:25"><p><strong>News and Information for the Data Integration community</strong></p></td> <td style="width:23%"> <div style="text-align:right"><!-- InstanceBeginEditable name="Month" --><p><strong>December 2006</strong><!-- InstanceEndEditable --></p></div></td> </tr> <tr style="background-color:#CCCCCC"> <td style="height:1; background-color:#999999" colspan="2"></td> </tr> </table></td> </tr> <tr> <td style="width:439; height:20" align="left" valign="top"><img src="/NewsLetter/line438x20.jpg" width="438" height="20" alt="" /></td> <td style="width:3; text-align:left; background-color:#CCCCCC" valign="top" >&nbsp;</td> <td style="width:158; background-color:#CCCCCC" rowspan="4" align="left" valign="top" > <table width="158" border="0" cellpadding="0" cellspacing="0" style="background-color:#CCCCCC"> <tr> <td style="width:158"> <!-- InstanceBeginEditable name="SideLinks" --> <table style="width:158; background-color:#CCCCCC" border="0" cellpadding="3" cellspacing="0" > <tr> <td style="width:158"> <p><a href="../../Solutions/SAPEnterpriseSuite/SAPAutomation.aspx">SAP related solutions</a><br /> <br /> <a href="../../Solutions/Sage/Automation.aspx">Sage Software related solutions</a><br /> <strong><br /> </strong><a href="../../Services/datamodernization.aspx">Data migration solutions</a></p> <p><font color="#000099">Software Labs showcased in a Microsoft <a href="../../Media/Microsoft Case Study.pdf" target="_blank">casestudy</a></font></p> <p><font color="#000099">Weekly Webinar on xFusion. Register via email to:<br /> <a href="mailto:info@softlabsco.com">info@softlabsco.com</a> </font></p></td> </tr> </table> <!-- InstanceEndEditable --></td> </tr> <tr> <td><p><a href="../nsunsubscribe.aspx">Newsletter Unsubscribe</a></p></td> </tr> <tr> <td style="background-color:#6699CC"><p><font color="#FFFFFF"><b>More Newsletters</b></font></p></td> </tr> <tr> <td><p><a href="../Summer08/NewsLetterSummer08.html">Most Recent</a></p></td> </tr> <tr> <td><!-- InstanceBeginEditable name="PrevNS" --> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td><p><a href="../nov06/newsletternov06.htm">Nov 2006</a></p></td> </tr> <tr> <td>&nbsp;</td> </tr> </table> <!-- InstanceEndEditable --></td> </tr> </table></td> </tr> <tr> <td style="height:32" align="left" valign="top"> <!-- InstanceBeginEditable name="InThisIssue" --> <table style="width:100%" border="0" cellspacing="0" cellpadding="3"> <tr align="left" valign="top"> <td style="height:25" colspan="2"><p><strong><a href="#FA">Build a clean address list from Microsoft Outlook Inbox<br /> </a></strong></p><p>When building an email address list, your inbox and sent items box is a valuable resource. But it is quite a tedious task to collect, filter, qualify, and de-duplicate email addresses from outlook manually.</p></td> </tr> <tr align="left" valign="top"> <td style="width:68%; height:192"><p><strong><a href="#AN">Announcements</a><br /> </strong></p> <p>Latest happenings in data migration and integration industry and xFusion.</p><br /> <p><strong><a href="#DK"><br /> Did you know?</a></strong><br /> <a href="#SU"><strong><br /> From the support desk</strong></a><br /> <br /> <br /> </p></td> <td style="width:32%" align="left" valign="top"> <div style="text-align:center"><img src="../DataClutter.jpg" width="230" height="187" alt="" /></div></td> </tr> </table> <!-- InstanceEndEditable --></td> <td align="left" valign="top" style="background-color:#CCCCCC">&nbsp;</td> </tr> <tr> <td style="height:20" align="left" valign="top"><a name="FA"></a><img src="../FA438x20.jpg" width="438" height="20" alt="" /></td> <td align="left" valign="top" style="background-color:#CCCCCC">&nbsp;</td> </tr> <tr> <td align="left" valign="top"> <!-- InstanceBeginEditable name="FA" --> <table width="100%" border="0" cellspacing="0" cellpadding="3"> <tr> <td style="height:120"><p><b>Build a clean address list from Outlook Inbox</b></p> <p>For marketing campaigns, it is sometimes desired to build the email list of all the people who you have interacted with. Your Outlook Address Book most likely contains just a subset of such an email list. Here are the steps to extract and build a clean SMTP ready email address list. You can use your favorite ETL tool to accomplish these steps. </p></td> </tr> </table> <!-- InstanceEndEditable --></td> <td align="left" valign="top" style="background-color:#CCCCCC">&nbsp;</td> </tr> <tr> <td style="height:38" colspan="3" align="left" valign="top"><!-- InstanceBeginEditable name="FA2" --> <table width="594" border="0" cellspacing="0" cellpadding="3"> <tr> <td align="left" valign="top"><p><b>Step 1: Extract Email Addresses from Outlook</b></p> <p> From within Outlook, select menu item File->Import and Export.<br /> i) Run the wizard. Export to a file<span style="font-size: large;">&#8594;</span>Comma Separated Values (Windows)<span style="font-size: large;">&#8594;</span>Inbox<span style="font-size: large;">&#8594;</span>C:\Temp\MyInbox.csv. <br /> ii) Click on "Map Fields" button and remove all the fields except From: (Name), From: (Address), To: (Name), To: (Address), CC: (Name), CC: (Address), BCC: (Name) and BCC: (Address). </p> <p><b>Step 2: Load C:\Temp\MyInbox.csv </b></p> <p>Using your favorite ETL tool, load C:\Temp\MyInbox.csv. You now have a table containing eight columns. </p> <p><b>Step 3: Normalize the table</b> </p> <p>We need to rearrange eight columns into just two columns - Names and Addresses. Within your ETL tool, break these eight columns into four tables of two columns each and then run a "Union" type transformation to append four tables into one. </p> <p><b>Step 4: Split multiple recipients into individuals</b> </p> <p>It is common to see an email being copied to multiple recipients. These recipients are separated by semicolons in the Outlook generated file. We need to split multiple recipients into individuals. Both the columns, Names as well as Addresses, would need to be split. </p> <p><b>Step 5: Merge names and addresses</b> </p> <p>We now need to merge all the columns from the previous steps into a new table containing the name and the corresponding email address. At the end of this step, we have a table containing the name and email address of each recipient.</p> <p><b>Step 6: Filter data with legal SMTP addresses</b> </p> <p>At this point, your table may contain some addresses that:<br /> </p> <ul > <li><p>are NULL (for example, when cc list is empty)</p></li> <li><p>are invalid SMTP addresses</p></li> </ul> <p>Use the following regular expression to filter out illegal SMTP addresses:<br /> <br /> <code><strong>^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$</strong></code> </p> <p><b>Step 7: Remove duplicates </b></p> <p>After merging from, to, cc, and bcc list, you now will be left with many duplicate entries. The last step is use the mechanism provided by your ETL tool to remove such duplicates. </p> <p>You now have a result that does not contain illegal email addresses or duplicate records.</p> <p>You can extend the mechanism to process your "Sent Mail" folder as well as other folders.</p> </td> </tr> </table> <!-- InstanceEndEditable --></td> </tr> <tr> <td style="height:1" colspan="3" align="left" valign="top"><a name="AN"></a></td> </tr> <tr> <td style="height:20" colspan="3" align="left" valign="top"><img src="/NewsLetter/AN600x20.jpg" width="600" height="20" alt="" /></td> </tr> <tr> <td style="height:38" colspan="3" align="left" valign="top"><!-- InstanceBeginEditable name="AN" --> <table width="100%" border="0" cellspacing="0" cellpadding="3"> <tr> <td> <p><b>Gain Remote Access to Your Data </b></p> <p>xFusion provides access to many data sources via HTTP and HTTPS, as well as industry standard Web Services. More and more companies require data to be exchanged with remote offices and business partners. xFusion offers a variety of ways to link important information in these environments. <a href="http://www.softlabsco.com/Products/WebDb/WebDBServer.aspx" target="_blank">Learn more</a></p> <p><b>Customer Success</b></p> <p> DM Review published an article recently on our xFusion software for CRM / ERP migration. <a href="http://www.dmreview.com/article_sub.cfm?articleId=1058075" target="_blank">Read the article online here</a></p> <p><b>New Partners</b></p> <p> We are happy to announce the addition of KBMS <a href="http://www.kbms.com" target="_blank">www.kbms.com</a> to our growing list of partners. xFusion will benefit them internally with customer migrations, as well as their customers through ongoing integration of company-wide information.</p> <p><b>New Customer Website</b></p> <p> Existing xFusion customers will benefit from our new Customer Interaction website. Many resources are available from this secure site including product and xFusion Acceleration Pack downloads, support resources, and links to our upcoming product forum. <a href="https://customers.xfusiondn.com" target="_blank">Login in here</a>.</p> <p><br /> </p></td> </tr> </table> <!-- InstanceEndEditable --></td> </tr> <tr> <td style="height:1" colspan="3" align="left" valign="top"><a name="DK"></a></td> </tr> <tr> <td style="height:20" colspan="3" align="left" valign="top"><img src="/NewsLetter/DYK600x20.jpg" width="600" height="20" alt="" /></td> </tr> <tr> <td style="height:32" colspan="3" align="left" valign="top"><!-- InstanceBeginEditable name="DYK" --> <table width="100%" border="0" cellspacing="4" cellpadding="3"> <tr> <td><p>Did you know that xFusion can easily format your data into HTML? xFusion Studio 3.0 has an integration utility to format data into HTML.<br /> <a href="http://main.xfusiondn.com/contributions/dataprocessing/a12060601.aspx" target="_blank">Read more</a><br /> </p></td> </tr> </table> <!-- InstanceEndEditable --></td> </tr> <tr> <td style="height:1" colspan="3" align="left" valign="top"><a name="SU"></a></td> </tr> <tr> <td style="height:20" colspan="3" align="left" valign="top"><img src="/NewsLetter/SUP600x20.jpg" width="600" height="20" alt="" /></td> </tr> <tr> <td style="height:32" colspan="3" align="left" valign="top"> <!-- InstanceBeginEditable name="SUP" --> <table width="100%" border="0" style="text-align:left" cellpadding="2" cellspacing="2"> <tr> <td> <p>Hi Support, </p> <p>We often need to sort a document and add line numbers by a grouping variable.<br /> SBO requires line numbering by groups for many of the Update functions and when adding new addresses and contacts.<br /> <br /> The original data looks like this before sorting:</p> <table width="90%" border="1" cellpadding="1" cellspacing="0" style="border-color:#FFFFFF"> <tr style="background-color:#CCCCCC"> <td style="width:36%">Heading</td> <td style="width:64%">Details</td> </tr> <tr style="background-color:#CCCCCC"> <td>APPLE</td> <td>John</td> </tr> <tr style="background-color:#CCCCCC"> <td>DELL</td> <td>Mary</td> </tr> <tr style="background-color:#CCCCCC"> <td>DELL</td> <td>Jim</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>Steve</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>Mike</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>Lynnette</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>Bob</td> </tr> </table> <p>This is the desired end result:</p> <table width="90%" border="1" cellpadding="1" cellspacing="0" style="border-color:#FFFFFF"> <tr style="background-color:#CCCCCC"> <td style="width:26%">Heading</td> <td style="width:10%">Line</td> <td style="width:64%">Details</td> </tr> <tr style="background-color:#CCCCCC"> <td>APPLE</td> <td>1</td> <td>John</td> </tr> <tr style="background-color:#CCCCCC"> <td>DELL</td> <td>1</td> <td>Mary</td> </tr> <tr style="background-color:#CCCCCC"> <td>DELL</td> <td>2</td> <td>Jim</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>1</td> <td>Steve</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>2</td> <td>Mike</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>3</td> <td>Lynnette</td> </tr> <tr style="background-color:#CCCCCC"> <td>IBM</td> <td>4</td> <td>Bob</td> </tr> </table> <p>It currently takes several queries to add this type of line numbering.<br /> Is there a function that will make this easier?<br /> <br /></p> <p><b>Support Desk:</b></p> <p>Hi,</p> <p>The functionality that you are looking for is called  Ranking. Rank function would serve this purpose.</p> <p>First define the column(s) that you wish to group on. Within that group, define the column(s) that you wish to sort upon along with the sort order.<br /> The query would look like this.</p> <p>Rank([[InputData]], array([InputData.Heading]),<br /> 0, [InputData.Details], true)</p> <p>Hope this helps!!</p> <p>Thanks...<br /> Support</p> </td> </tr> </table> <br /> <!-- InstanceEndEditable --></td> </tr> <tr> <td style="height:45; background-color:#CCCCCC" colspan="3" align="left" valign="top" > <div style="text-align:center"><p>Software Labs, Inc. 1225 Pleasant Grove Blvd. #100, Roseville CA 95768, USA<br /> <b>ph</b>: 1-916-773-6272 <b>fax</b>: 916-773-6281 <b>web</b>: <a href="http://www.softlabsco.com">http://www.softlabsco.com</a> <b>email</b>: <a href="mailto:info@softlabsco.com">info@softlabsco.com</a><br /> <b>unsubscribe via email</b>: <a href="mailto:unsubscribe@softlabsco.com">unsubscribe@softlabsco.com</a> <b>unsubscribe via web</b>: <a href="http://www.softlabsco.com/newsletter/nsunsubscribe.aspx">click here</a></p></div></td> </tr> </table></td> </tr> </table> </body> <!-- InstanceEnd --></html>