Notes Domino 6 Application Development Update
Notes Domino 6 Application Development 1
Notes Domino 6 Application Development 2
Notes Domino 6 Certified Developer Package


Using a Format (COL) File

A COL file is a ASCII text file used to define data that is imported into Notes. In addition, Notes formulas can be defined in the COL file to set other fields or translate the imported data. Importing data is done in the Notes client, a Domino Designer client is not required. Any user can import data into a database if they have the proper security privileges.

There are two steps to import data into a Notes database using a COL file:

1. Create the COL file to define how the imported data will be parsed in the Notes database.
2. Import the data into the Notes database using the COL file.

Step1: Creating the COL File

Create a COL file by using Notepad or any text editor. Provide a filename and use the ".col" extension.

The first section of the COL file defines the imported data. Each line of the COL file is used to define a Notes field to import the data into. Enter the fieldname used on the Notes form followed by a colon(:). If importing a 1-2-3 file than the worksheet columns are defined by using the "WKSCOL" keyword. The following example shows five columns from a 1-2-3 worksheet being imported into the Notes database. In this example column A in the worksheet will be put into the date field, column B into the chknum field, etc. Notes will determine the datatype based on the appearance of the data.

1.	date: WKSCOL A
2.	chknum: WKSCOL B
3.	amount: WKSCOL C
4.	deposits: WKSCOL F
5.	withdrawals: WKSCOL G
6.	balance: WKSCOL K

To define the datatype use the DATATYPE keyword. For example, to define the information in column A of the worksheet as a DATETIME use the following:

date: TYPE DATETIME WKSCOL A

1.	coursename: UNTIL ",";
2.	first: UNTIL ",";
3.	last: UNTIL ",";
4.	startdate: UNTIL ",";
5.	enddate: UNTIL "";

The final step in creating a COL file is optional. Formulas can be specified to create additional fields or to adjust the imported data. Formulas are listed between the FORMULASTART and FORMULAEND keywords in the COL file. In the example below, the data in the first and last fields is propercased and trimmed. The form field is set to "course" and a date field, CREATEDVIACOLFORM is set to the current data and time. The FULLNAME field is also populated by concatenating first and last fields.

6.	FORMULASTART
7.	FIELD first:=@propercase(@trim(first));
8.	FIELD LAST:=@propercase(@trim(last));
9.	FIELD Form :="course";
10.	FIELD CREATEDVIACOLFORM := @NOW;
11.	FIELD FULLNAME := FIRST + " " + LAST;
12.	FORMULAEND

Step2: Importing the Data using a COL File

Follow these steps to import the data into the Notes database using the COL file.

1. In the Notes client, open the database to a view where the new documents will be imported. Choose File - Import from the menu.
   
   
2. Choose the data file to import and click the [Import] button.
   
   
3. Choose the form to use to display the imported documents. Check the box to "Use format file". Click the [Choose format file...] button and select the COL file to use for the import. You can optionally set the 'Calculate fields on form during document import' option. If this option is set, the field formulas on the form are calculated when the data is imported and before the documents are saved in the database. Click [OK] to start the importing.
   
   
4. The imported documents are displayed in the view. You should always test importing data into a copy of a database before using this approach in a production database.
   
   

Example - Importing a CSV File

Follow these steps to download and install an example Notes database, COL file, and text file of data to be imported. This example will import text in a CSV format and use a COL file to trim and propercase the data. Note that this database will only work on a Notes 6 client. The COL file will be explained at the end of this example.

1. Click on the attachment below to download this self-extracting zip file. Extract the contents of the zip file to your Notes data directory.
   
   
2. In Lotus Notes, open the COLIMPORT.NSF file which you extracted in step 1.
3. Select File | Import from the Notes menu and select the COLDATA.TXT file that was extracted in step 1.
4. Format the input data using the IMPORTCSV.COL file that you extracted in step 1. Check the option to calculate the fields on form during document import.
5. Click [OK] to start the import. The documents will appear in the view.

The data in the COLDATA.TXT file that was imported is:

Introduction to WebSphere Studio 5,john , smith,04/01/2004,10/01/2004

Introduction to WebSphere Studio 5,Barbara,Jones,04/01/2004,10/01/2004

Introduction to WebSphere Studio 5,Ralph,Dempsey,04/01/2004,10/01/2004

Introduction to WebSphere Studio 5,Jerry,Johnson,04/01/2004,10/01/2004

Notes Domino Application Development 1,George,Gensen,05/03/2004,12/31/2004

Notes Domino Application Development 1,Wendy,Sands,05/03/2004,12/31/2004

Notes Domino Application Development 1,Sandy,Watson,05/03/2004,12/31/2004

Notes Domino Application Development 1,Keith,Walton,05/03/2004,12/31/2004

Notes Domino Application Development 1,Bill,Seymour,05/03/2004,12/31/2004

The IMPORTCSV.COL file used in this example is described below.

1. Lines 1 to 5 define the fields in the Notes database for the data to be imported. The comma is used as a separator except for the enddate field. Since there is no separator (comma) at the end of each line the enddate is determined by using the null string. The coursename, first, and last fields force the data type to be text.

1.	coursename: DATATYPE TEXT UNTIL ",";
2.	first: DATATYPE TEXT UNTIL ",";
3.	last: DATATYPE TEXT UNTIL ",";
4.	startdate: UNTIL ",";
5.	enddate: UNTIL "";

2. Line 6 starts the formula section of the COL file. Lines 7 and 8 performs a @Propercase and @Trim on the first and last fields to trim excess spaces and propercase the input. Line 9 sets the form field on the imported documents to "course" so the proper form is used to display the documents. Line 10 creates a new field called CREATEDVIACOLFORM and sets this field to the current date and time. Line 11 creates a new field called FULLNAME and sets the value to be the first name followed by a space followed by the that name. Line 12 ends the formula section and is the last line of the COL file.

6.	FORMULASTART
7.	FIELD first:=@propercase(@trim(first));
8.	FIELD LAST:=@propercase(@trim(last));
9.	FIELD Form :="course";
10.	FIELD CREATEDVIACOLFORM := @NOW;
11.	FIELD FULLNAME := FIRST + " " + LAST;
12.	FORMULAEND