PSIcapture Walkthrough: Configuring Text (TXT) Files for use in Lookups

Introduction

When attempting to configure a Lookup using a text file as your data source you may encounter Failed Lookups because of data type misclassification by the database provider used to query text files.

The following will guarantee proper data type classification in order to avoid data type ambiguity or misclassification. 

While you can wait and resolve failed lookups with the procedure below, it is recommended to explicitly define Data Types anytime you plan on using a text file as your data source for Lookups. Doing so should guarantee proper data type classification, avoid any ambiguity or misclassification in your Lookup and provide for a Configuration that is more easily understood and maintained.

Configuration Steps

1. Examine your TXT data file as you configure your Lookup:
   
2. After configuring your Lookup, open the Schema.ini file that is created.  (Note:  It is located in the same directory as the selected text data file)
3. Find the section in the Schema.ini file that looks like the following:  [FileName.txt]
4. Enter the Column, Field and Data Type definition beneath the Format=Delmited() line so that it looks like this:
    

Testing Steps

1. For our example, the Schema.ini file in its “original” state will appear as follows:
   
   If, for example, your “Key” column is Col1 and has values that are a mixed bag of number and text (i.e. SX000123 and 0000072961 – see Configuration Steps #1 above), then your Lookup will probably fail. 
2. Run a batch through the Index Step with the Schema.ini as in Step1 above and see the Lookup fail:
    
3. Edit your Schema.ini file using the Configuration Steps above to specify your data types
4. Run a new batch through using the detailed Schema.ini and see it succeed: