Text Lookup and Schema.ini File

Introduction

When attempting to execute a lookup using a txt file as your data source, one may encounter “Failed” lookups because of data type misclassification by the txt search provider.

For instance, since no data types can be assigned directly to the columns within the text files, by default the Microsoft Text driver has to try and interpret the type of data in each column based on the data. Many times this can cause the data coming in to PSIcapture to not be what the user is expecting. In order to guarantee the data coming in is exactly what the customer wants, they need to specify the data types of each column within the Schema.ini file that we auto create in the folder containing the csv file.

The following link will explain in more detail how to set up the Schema.ini file. Specifically look at the section entitled “Specifying the Fields”: http://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx

OR

You can follow the guide below to help prevent data type ambiguity or misclassification.

Step-by-step guide

  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 your designated txt 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

  1. For our example, the Schema.ini file in its “original” state will appear as follows. NOTE: 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 Step 1 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:
     

Conclusion

While one can wait and resolve “Failed Lookups” with the procedure above, it is probably a “best practice” to explicitly define Data Types anytime you plan on using a txt 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.


If you are still experiencing difficulties, please contact our Support team by:


Table of Contents