RSS

Navigation





Search the wiki
»

PoweredBy

itrezzo Technical Support Wiki

Alternate Data Source - Microsoft Excel Data Source



Excel Data Sources

Many organizations will keep critical business contacts stored in an Excel Spreadsheet. In most cases this information is not accessible unless you share out the file and there is not an easy way to extract specific contact details. In addition, this information will not synchronize with a handheld device unless contacts are manually added to a user's contacts. This allows for contacts to fall out of date and erroneous information to be present.

Setting up Excel Data as an Alternate Contact Source in itrezzo

  1. Open the itrezzoAgent Administrator
  2. Expand the EPS Container
  3. Right-click on Alternate Contact Source
  4. Select 'Add Data Source'

    Right click on Alternate Contact Sources and choose Add Data Source

    Right click on Alternate Contact Sources and choose Add Data Source


  5. Type in the name you would like for this Alternate Contact Source
  6. Click on the 'Edit' button; Connection String window will appear
  7. Click the 'Connection Type' dropdown and select the appropriate data connection type (Excel 2007 or Excel 2003 and earlier)

    Choose connection type 'Excel 2003 and earlier or Excel 2007'

    Choose connection type 'Excel 2003 and earlier or Excel 2007'


  8. In the file location field, specify the path where you stored the contact Excel file

    Specify the contact excel file location with filename

    Specify the contact excel file location with filename


  9. Select 'OK'
  10. You may optionally select the 'Test' button to test your connection
  11. Upon successful connection, you should see a similar 'successfully connected' message.

    Successfully connected using the Connection String

    Successfully connected using the Connection String


  12. Enter your query string for the data you wish to obtain.
  13. An example of a connection and query string is given below:

    Name: Excel Data Source
    Description: Example Excel
    Connection String:
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\sj2-ex2.itrezzo.net\transfer\ddk\Contact Export.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
    Query String:
    Select [EmailAddress] as [Email1],
    [FirstName],[LastName],
    [Company],
    [Department],
    [JobTitle] as [Title],
    ([FirstName]+ ' ' + [LastName]) AS [DisplayName],
    [User1] AS PIN
    from [Contacts$]
    WHERE [EmailAddress] IS NOT NULL



    Configuring Alternate Data Source - Using Microsoft Excel

    Configuring Alternate Data Source - Using Microsoft Excel


  14. Select 'Run' to view the results of your query
    Click on Run to verify the query string

    Click on Run to verify the query string


  15. Select 'OK'



Setting a Excel data sources as a Mandatory Contact List

  1. Open the itrezzoAgent Administrator
  2. Expand the EPS Container
  3. Expand Alternate Contact Source
  4. Right-click on the Data Source
  5. Select 'Make Mandatory Contact List'

    Right click on the added Excel - ACS and choose 'Make Mandatory Contact List'

    Right click on the added Excel - ACS and choose 'Make Mandatory Contact List'


  6. Click on 'Select' button to choose Target Authorized Recipients.
  7. Select from the Global Address List (GAL) users or Active Directory (AD) distribution lists you would like to push the public folder contacts to

    Select GAL users or AD Distribution Lists as Target Authorized Recipients

    Select GAL users or AD Distribution Lists as Target Authorized Recipients


  8. Select 'OK'

Note: It may be important for an admin to select the 'Include contacts with no email address' option when using a Alternate Contact Source as a Mandatory Contact List. There may be instances where there is no primary email address and contacts will still need to be pushed to users.

Sample MS Excel file

  • A sample Microsoft Excel file is available here for download.

itrezzo Support Wiki version 3.0.5.619. See the itrezzo web site or the itrezzo blog site to learn about contact management on your smartphone.

The Trademark RIM® BlackBerry® Smartphone and BlackBerry® Enterprise Solution is owned by Research In Motion Limited and is registered in the United States and may be pending or registered in other countries. The owner of this wiki site is not endorsed, sponsored, affiliated with or otherwise authorized by Research In Motion Limited.

Microsoft Outlook, Microsoft Exchange Server, Microsoft Active Directory, Microsoft Windows Mobile are trademarks of Microsoft.