Using a column descriptor file (COL ) to map a source file to a Notes® view

To import many files that have an identical structure into a view, create a column format descriptor (COL) file. A COL file is like a map that defines which data from the source file goes into which Notes® field. Use a COL file only with tabular text files and worksheet files.

A COL file is an ASCII text file that contains a series of column definition statements, followed by a section containing formulas that control the contents of fields in the Notes® view. You write one statement for each column in the source file. This statement describes how to format and map the columns in the source file to columns in the Notes® view. For best results, use exactly the same field names in the COL file and in the Notes® view.

Mapping a column to a field in a view

Enter the name of the column in the source file. The name should be identical to the name of the corresponding field in a form. After you enter the column name, you can provide an optional statement that specifies the data type of the data. You must, however, provide statements that define the format of the source data -- that is, the delimiter or fixed position of the column.

If you use field names in the COL file that are not in the Notes® view, Notes® imports the data for those fields, but the data won't appear in the form or view until you create Notes® field names that exactly match those in the COL file. For example, if the COL file uses the field name "Phone" and the Notes® view uses "PhoneNumber," data for the "Phone" field is present in the view but is not displayed. To correct this problem, change the name of the existing Notes® field to "Phone," create a synonym for the existing field, or create a new Notes® field called "Phone," and refer to the field in the view you're using.

Specifying the data type of a column

When you import a file, Notes® automatically determines the data type of each column, based on the appearance of the data. For example, if the column in the source file contains data in the form 7/19/97, Notes® imports the data as dates.

As part of a field definition, you can provide an optional TYPE expression, followed by the appropriate data type: Text, Number, or Datetime. For example, to import the contents of a field called DateOfBirth as a date, enter the following field definition into the COL file:

DateOfBirth: TYPE DATETIME

The data in the source column must fit the data type you specify. If Notes® encounters data that doesn't match the data type you specify, Notes® imports the data as the type it most resembles.

Using formulas

The formulas that you write in a COL file describe how to modify the incoming values for display in the view. These formulas, which follow the same rules as view selection formulas, may:

  • Use @If formulas to test incoming values and display a specific value in the view, depending on the result of the formula.
  • Use text manipulation functions to modify the appearance of the incoming value -- for example, to concatenate values or trim excess space characters from incoming values.
  • Enter default values in fields.