RSS

Navigation





Search the wiki
»

PoweredBy

itrezzo Technical Support Wiki

Alternate Data Source - CSV File



CSV/Text Data Sources

There are some cases where critical contact information has been created in a delimited text file or CSV file. Rather than sharing the file and manually importing the CSV into dozens or hundreds of mailboxes, there is an easier way to deliver the specific contact details.

By creating an itrezzoAgent Alternate Contact Source with this CSV file as the source, this information can become the source for a Mandatory Contact List and be pushed to thousands of mailbox contact folders where it will also synchronize with BlackBerry, iPhone, Android, or other handheld devices. Subsequent updates made to the CSV file will also be incrementally updated on each mailbox.

Configuring CSV or Text Data as an Alternate Contact Source

  1. Place the CSV file into a shared folder. Make sure itrezzoAgent service account has at least read access to that shared folder and files inside.
  2. Open the itrezzoAgent Administrator
  3. Expand the EPS Container.
  4. Right-click on Alternate Contact Source and select 'Add Data Source'

    Right click on Alternate Contact Source and choose Add Data Source

    Right click on Alternate Contact Source 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 (Text including CSV and TXT)

    Click on the dropdown arrow and choose 'Text including CSV and TXT'

    Click on the dropdown arrow and choose 'Text including CSV and TXT'


  8. Enter the UNC path of the shared folder (you created earlier) in Folder Path field

    Enter Network shared path as the folder path

    Enter Network shared path as the folder path


  9. Leave the default format type as Delimited. Click on '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:CSV data source
    
    Description: Provide any appropriate descriptive name to this data source
    Connection String: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\sj2-ex2\transfer\ddk\datasources;Extended Properties="text;HDR=Yes;FMT=Delimited";
    Query String: Select *, [EmailAddress] AS Email1,[Email2Address] AS Email2 from [Contact Export.csv]


    Configuring query string for CSV

    Configuring query string for CSV


  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'

Note: If the CSV file contains dotted numbers, e.g. 408.444.1212, that numeric value would be treated as a floating point, and the last .1212 would be ignored. To avoid that, place a schema.ini file within the same shared folder where the CSV file is located. Follow this section how to create a schema.ini file





Setting CSV or Text data sources as a Mandatory Contact List

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

    Right click on the added CSV Data Source and choose 'Make Mandatory Contact List'

    Right click on the added CSV Data Source 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 a user and group of users from GAL or AD

    Select a user and group of users from GAL or AD


  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 Data Sources as a Mandatory Contact List. There may be instances where there is no primary email address yet contacts will still need to be pushed to users.



Using Schema.ini

The contact import process utilizes Microsoft.Jet.OLEDB provider when importing the data from external sources such as a CSV or Text file. Difficulties arise as the external data file may contain columns with different data types. Basically, the provider cannot differentiate data types between the columns and rows. Thus, it blindly considers field types based on the first few rows and truncates data which does not match the assumed data type. To overcome this problem, use a schema file schema.ini defining the data type within the CSV or text file and thus allowing the provider (Microsoft.Jet.OLEDB) to read and recognize the exact data types of each column.

Points to remember before creating Schema.ini

  • The schema information file, must always named as 'schema.ini'.
  • The schema.ini file must be kept in the same directory where the CSV or Text file exists.
  • The schema.ini file must be created before reading the CSV file.
  • The first line of the schema.ini, must be the name of the CSV file, followed by the properties of the CSV file, and then the properties of the each column in the CSV file.

For demonstration purposes, in the network share where you would put the CSV file, use notepad and populate the following lines in schema.ini.

[Sample-TXT-Data.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI


[Sample-CSV-Data.csv] 
ColNameHeader=True 
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI 
DecimalSymbol=~

 Use notepad to create a file schema.ini

Use notepad to create a file schema.ini






Sample CSV file

  • A sample CSV 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.