If you plan any type of data migration or recurring data transfers to your Lotus Notes databases, you should be aware of the native Notes import function, which allows you to transfer almost any type of data into your Notes database without much effort. Although the function has a few limitations, it should be sufficient for many basic import jobs. Generally, the import method works by parsing and identifying comma-separated field values contained in an ASCII text file to correspond to columns in a Notes view. This means that you must first transfer your source data from the AS/400 (or other RDMS) to the ASCII text file.

In this article, I’ve used a working example to show you how to import a tabular text file. For the example, I’ve used data from the infamous sample AS/400 Client Access file QIWS/QCUSTCDT and transferred the file to my PC as a comma-delimited file. (I’m assuming you already know how to get data from your AS/400 to a comma-delimited file on your PC, so I don’t go into detail about how to do that.)

Importing Tabular Text

Tabular text is ASCII text arranged in rows and columns and separated by characters (e.g., commas or semicolons). This file type is also referred to as a delimited text file. When you import a tabular text file into a Notes view, each row of text becomes a Notes document and each column of text (field) becomes a Notes field. If a Notes view does not already exist that contains each field in the ASCII text file, you need to create a special Notes view to receive the data that you’re importing. This special view should contain a column for each field in your ASCII text file, and the columns should be in the same order as the corresponding fields of the ASCII text file occur. If you map the fields properly, the value in each text field becomes the value of the corresponding Notes field in the view selected at time of import.

The Notes import function automatically determines the field data type. For example, if an incoming field value is all numbers, the import function creates a number data type, and if the incoming field value is all characters or a combination of numbers and characters, it creates a text data type. If the field contains a date value, the import function creates a date data type. The data type as defined in Notes does not affect the data type created. In other words, if you map a date to a field defined in Notes as a nondate data

type, Notes stores the value as a date data type. Be careful about how you map and import data and make sure that incoming data values would normally be data types that match the fields you’re mapping to.

Figure 1 lists the ASCII text file discussed in this article. The file is comma- delimited and does not use quotes to designate character strings. In addition, the fields in the ASCII text file are identified only by value, not by name.

Creating a Column Descriptor File

The Lotus Notes documentation states, “If the ASCII file and the Notes view have different formats, create a column descriptor file (.COL) to parse the ASCII file so its individual components correspond to columns in the Notes view or document fields.” This implies that you can perform an import without a column descriptor file, although I was not able to do this successfully. With a column descriptor file, however, I was able to successfully import data as illustrated in this material.

A column descriptor file is an ASCII text file (with a .COL extension) that contains a series of column definition statements that define which data from the source file goes into which Notes field. It can be used to import tabular text files and Lotus 1-2-3 worksheet files. The sample import in this article uses tabular text. This column descriptor file contains specifications that define the name of the view columns (fields), the separator characters used (commas in this case), and any calculated fields derived from formula expressions. Figure 2 contains the column descriptor file used for the working example in this article.

All comments begin with semicolons; other comments are interspersed in the specifications. After the initial comment are the column descriptor statements, whose syntax is as follows:

column_name: UNTIL “separator_character”;

You must define the columns in the same order in which they occur in the ASCII text file. The column_name is the name of the field in the Notes view that receives the value of the source field and is followed by a colon. UNTIL indicates that the value for column_name continues until the statement encounters the separator_character, which is simply the actual character used in your ASCII text source file to separate field values. In Figure 2, you can see that the separator character is a comma.

After you specify all column descriptors, you have the option of calculating your own values by using the Notes formula language. To do this, you must surround the formula statements with a FORMULA-START statement and a FORMULAEND statement (Figure 2).

As you can also see in Figure 2, the CREDITDUE field is set to Yes or No based on the value of the CREDITAMT field. If the value of CREDITAMT is greater than one dollar (1.00), CREDITDUE is set to Yes. Otherwise, CREDITDUE is set to No.

Limitations

As I mentioned, this method should be sufficient for basic data import tasks. It is one of the simplest ways to get data into Notes. However, along with its simplicity come a number of limitations. Notes supports a maximum of 999 characters per text file record. If any of your text file records exceed 999 characters, you must either use a different import method or split the data into multiple records by using multiple files and performing multiple import steps. In addition, if your source text file contains quotes around character strings, you must remove the quotes to prevent them from also surrounding the string value that ends up in your Notes field. (That is undesirable in most cases.) If any of the fields in your source text file contains characters equivalent to the separator character you’re using (in this

sample, commas), the import function will interpret them as separator characters. You’ll need to temporarily replace them with a non-separator character or remove them.

Performing the Import

After you create the ASCII text file containing your source data, your descriptor file and optionally, the view that is to receive the data, follow these steps to perform the import:

1. Select your target Notes database and open the view that you want to receive the data from your ASCII text file.

2. Select Import from the File menu.

3. Select Tabular Text and the name of the source file from the Import dialog.

4. Click Import to display the Tabular Import Text dialog.

5. Select a form to use for the imported source data from the Use Form list. (This form is associated with the document created for each imported record and is normally the same form used in the view that receives the data.)

6. (Optional) Enter the header line count or footer line count. (This might be useful when you use reports that contain headers and footers as your source data.)

7. Enter the number of lines per page in the source file. (I have found that this value is not required when the source file has no headers or footers.)

8. Select Main Document(s) (the default) from the Import as list unless you are creating response documents.

9. (Optional) Select “Calculate fields on form during document import” if you have calculated fields derived from formula expressions in your column descriptor file.
10. (Optional) Check the “Use format file” checkbox if you have created a column descriptor file as done in the example in this article. Then, click “Choose format file” and select the column descriptor (.COL) file you created for import.
11. Click OK to execute the import. After the import completes, the Notes view should be populated with the imported data (see Figure 3).

You Always Have Options

Although I’ve used a comma-delimited tabular text file with variable-length fields, you can import fixed-width fields with column descriptor statements that allow you to specify field start and end positions or start positions and width. You can also import structured text files or Lotus 1-2-3 worksheets to Notes data. Information about these other import options is available in the Help database in Notes.

Before you employ more complex and possibly more expensive ways to import data to Lotus Notes, consider the Notes native import function. It may be a good choice for simple one-time data migration or simple, regularly scheduled imports from other databases that you can’t connect to your Notes database via ODBC or Notes Pump (now known as LEI).

938472,Henning,G K,4859 Elm Ave,Dallas,TX,75217,5000,3,37.00,0.00
839283,Jones,B D,21B NW 135 St,Clay,NY,13041,400,1,100.00,0.00
392859,Vine,S S,PO Box 79,Broton,VT,5046,700,1,439.00,0.00
938485,Johnson,J A,3 Alpine Way,Helen,GA,30545,9999,2,3987.50,33.50 ; Column description specs for data import to Notes view
CUSNUM: UNTIL “,”;

LASTNAME: UNTIL “,”;
INITIAL: UNTIL “,”;
STREET: UNTIL “,”;
CITY: UNTIL “,”;

Figure 1: This is a partial listing of the comma-delimited file used for this article’s sample import job.

STATE: UNTIL “,”;
ZIPCODE: UNTIL “,”;
CREDITLIMIT: UNTIL “,”;
CHARGECODE: UNTIL “,”;
BALANCEDUE: UNTIL “,”;
; Identify end of last field as null (“”)
CREDITAMT: UNTIL “”;
; Specify formulas after all column definition statements
FORMULASTART
; Create value for credit due
FIELD CREDITDUE := @If(CREDITAMT > 1.00; “Yes”; “No”);
FORMULAEND

Figure 2: This column descriptor imports the comma-delimited file in Figure 1.

Figure 3: The CustInfoView now contains data imported from the text file.

Also Read

What Does Domino 10 Really Mean?

The Domino 9.0.2 Waiting Game

Technology Focus: Employee Aids for Internal Communications