Defining Source Files

Text Input Files

Data in a text file can be organized in several ways.

Single Line per Record

In text files, a record consists of one or more fields terminated by a unique character or set of characters. Usually a record is simply a single "line" of text terminated by a new-line-character. This one record per line format is the most common and is easily manipulated using a Text Connection. The format of such a line entry, for the three sample entries shown, would be as follows:

IDNO-----NAME--------------TEL-------BALANCE----PAYMENT----DATE

The following three sample entries show input data organized in the one record per line format. These data entries adhere to the format shown previously. Note that the preceding line is shown here for your information only and should not actually be included in the example data file.

566238744Carol Ann Wilson  1025557746000040000980000003000010-15-93
012358743Nathan Varberg    1015552345000003000000000002500010-11-93
524135698David Fein        1015553222000035599550000010255009-30-93

This example of a customer record includes a unique ID number, the customer name, telephone number, balance due, payment, and date. We use this input data as the basis for the examples in the next sections.

Fixed Field Version

Using the data in the preceding sample input file as an example, the following example builds the ZID field entries necessary to process the data using HCL Enterprise Integrator (HEI). For simplicity, all fields in this example are fixed, that is, they are all in the same location in every record. In addition, for purposes of visual clarity in this example, initial caps indicate entries you make, specific to your data.

In the following example, the first line denotes the field named Type as Customer. The first line illustrates the use of the VALUE field option for inserting literal strings into fields. This parameter inserts "Customer" into the Type field.

The first data field (the second line) in our example is a customer ID number (labeled IDNO). The IDNO field could be described as a number or a text field. Because the value here is for identification purposes and does not represent a quantity or magnitude, we define the IDNO field as text. In this example, the IDNO field always begins in column 1 (the first position in the record) and always ends in column 9. The Connector inserts this data into the field named IDNO.

The ZID field description in our first example look like this:

Type: TYPE TEXT VALUE "Customer"
Idno: TYPE TEXT START 01 END 9
Name: TYPE TEXT START 10 END 29
Tel:  TYPE TEXT START 30 END 39
Balance: TYPE NUMBER.2 START 40 END 50 
Payment: TYPE NUMBER.2 START 51 END 61
Date: TYPE DATE START 62 END 67 FORMAT MM.DD.YY

The START and END field options with their corresponding numeric values describe the beginning and ending position of the input fields (note that you could use the WIDTH or LENGTH field options instead of the END field option). The decimal place (.2) notation after the TYPE NUMBER specification tells the Text Connection how many decimal places are implied in the input numeric value. If you do not configure the decimal place notation, the Text Connection copies the input numeric value as an integer.

Creating ZID field entries for date and time values is more complex than simple TEXT or NUMBER types. Dates and times come in various formats with unusual field separators, prefixes, and suffixes. The Text Connection allows for these various formats, but requires that you describe the input date format with a special FORMAT field option specification. Notice the format specified in the DATE field in our example.

The FORMAT field option identifies the various components of an input date and/or time field. In this example, M designates a month field, D designates a day field, and Y designates a year field. Refer to the FORMAT field option for more information on time and data formats.

Floating Field Version

Another typical format for input data is the floating field format. A field is considered to be floating if its position is determined by the end of the field immediately before it in the input record. The example shows data from our fixed field example as a floating field format file. Each field is separated from the subsequent field by a comma:

IDNO,NAME,TEL,BALANCE,PAYMENT,DATE
566238744,Carol Ann Wilson,1025557746,4000098,30000,10-15-93
012358743,Nathan Varberg,1015552345,300000,25000,10-11-93
524135698,David Fein,1015553222,3559955,102550,09-30-93

For the floating field version, configure the ZID field description as follows:

Type: TYPE TEXT VALUE "Customer"
Idno: TYPE TEXT UNTIL ","
Name: TYPE TEXT UNTIL ","
Tel:  TYPE TEXT UNTIL ","
Balance: TYPE NUMBER.2 UNTIL ","
Payment: TYPE NUMBER.2 UNTIL ","
Date: TYPE DATE FORMAT MM.DD.yy

The IDNO field begins in column 1 and continues until a comma is encountered in the input data. Subsequent fields are processed similarly. In the DATE field specification, notice that the last field in the record does not have an UNTIL specification. The last field in a record does not need an UNTIL specification because scanning for the end of the last value in a record always stops at the end of the data record.