Navigation 

• Main Page
  

• Random Page
• Create a new Page
• All Pages
• Categories
  

• Administration
• File Management
  

• Login/Logout
• Your Profile
• Create Account
  

Search the 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 users 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

   
   
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. Left 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'

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

   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

    
    
12. Enter your query string for the data you wish to obtain.
    
13. An Example of 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

    
    
14. Select Run to view the results of your query
    

    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 the Data Source
5. Select 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

   
   
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.