Constructing File Layouts and Performing Data Interchanges

This chapter provides an overview of file layouts and discusses how to:

• Construct file layouts.

• Specify file layout, record, and field properties.

• Perform data interchanges.

• Produce a sample file layout.

Understanding File Layouts

This section discusses:

• File layouts.

• Field formats in a flat file.

• Fixed-position (FIXED) format files.

• Comma-separated value (CSV) format files.

• XML-tagged (XML) format files.

Understanding File Layouts

A file layout is a definition that represents the data structure of a flat (text) file to be processed. When reading from or writing to a flat file, the PeopleCode can use a file layout as a template to identify and correctly process the file’s records and fields. File layouts work with hierarchical and nonhierarchical data, and they can handle files that combine data records with nondata (audit or control) records.

To access data in a file, you don’t have to create a file layout. PeopleTools supports reading and writing to plain text files, as well as to files that have a format based on a file layout.

• If the file is a plain text file, data is read or written by using text strings.

• If the file is based on a file layout, you can use text strings, rowsets, or record objects.

Using a file layout greatly simplifies reading, writing, and manipulating hierarchical transaction data with PeopleCode. It can facilitate transactions between a PeopleSoft application and a third-party system when the third-party system doesn’t support PeopleSoft Integration Broker or component interfaces.

Applying File Layouts to Data Interchanges

In addition to manipulating transaction data, you can employ file layouts to move data between a PeopleSoft database and external systems (data interchange) by using flat files as the transmission medium. File layouts enable you to:

• Export hierarchical PeopleSoft data to several flat file formats.

• Map incoming hierarchical file data directly to PeopleSoft tables.

• Preview and troubleshoot the input data and its formatting before importing it.

• Automatically generate the Application Engine and PeopleCode programs that are needed to perform data import.

• Use batch processes to perform large-volume data imports and exports.

See Also

Performing Data Interchanges

File Class

Field Formats in a Flat File

A flat file, in the simplest sense, is a collection of fields in text format. The file must be formatted in a way that enables the PeopleCode to locate each field. PeopleSoft file layouts support three formats:

• FIXED: Each field has a starting position and a length that together specify its location in the file.

  This is the default format for new file layouts.

• CSV: Fields are located in the file by their sequence, separated by commas.

• XML: A field is located not by its position or sequence within a record, but by the named XML tags surrounding it.

To preserve relationships between fields, you group fields into logical collections. In relational databases, these collections are records. Each line within a file is a collection of fields.

Note. With some file formats, the logical concept of a record may actually span multiple physical lines, but the concept of collections of fields remains.

The conceptual structure of all three file formats is represented in a file layout as follows:

File layout structure

A file layout is a collection of file records, which in turn are a collection of fields, each of which has a describable location.

Note. To avoid confusion with the standard terms record and field, when working with a file layout we refer to a collection of fields as a file record and to fields as file fields.

FIXED Format Files

This is the most common type of flat file that is currently processed by PeopleSoft EDI Manager. Almost all electronic data interchange (EDI) type processing uses this file type where each data element is oriented by a fixed, or column-dependent, position within the file.

The following table describes the properties of FIXED format files:

Considerations for FIXED Format Files

Consider the following when working with FIXED format files:

• Be careful when you change the length or starting position of any file fields, or when you insert a new file field between two existing ones.

  It’s possible to overlay fields. Results are unpredictable.

• When you insert a record into a file layout, fields of the Long type are converted to the Character type, with a length of 0.

  You must set the field length to a number greater than 0 before you can save the file layout.

CSV Format Files

In this type of file, each data element is surrounded with a separator, a delimiter, or both. File record IDs can be used to determine which table data is moved to or from; however, in most cases this type of file contains homogenous records.

In the following example, the qualifier is a double quote (") and the delimiter is a comma (,):

"NAME","ADDRESS","PHONE" 

File layout definitions store the file record ID (when used) and the relative sequence number of the field. In the preceding example, "PHONE" is sequence number 3.

The following table describes the properties of CSV format files:

Considerations for CSV Format Files

Consider the following when working with CSV format files:

• Both the qualifier and the delimiter accept a blank as a valid value.

• If a field is null, you don't have to use qualifiers.

  In the following example, Field2 is null:

  Field1,,Field3,Field4. . .

• The system automatically pads the decimal places with zeros when amounts do not have values beyond the decimal point.

  For example, 100 is automatically written as 100.00.

Parsing Support for CSV Format Files

The following list summarizes parsing support in preview and runtime for all CSV file types:

• All blank lines are ignored.

  In the past, blank lines came in as empty records.

• All empty fields are returned as empty strings or zeros.

• Fields, including commas or new lines, are surrounded in quotes.

• When a field is in quotes, any embedded quotes must be escaped by two quotes.

• When non-Microsoft Excel format backslashes are embedded within a string, they must be double-backslashes to be interpreted as a backslash; otherwise, they are interpreted as an escape character.

Parsing Support for Microsoft Excel CSV Format

The following table summarizes the differences between Microsoft Excel CSV parsing and non-Microsoft Excel CSV parsing:

XML Format Files

This type of file contains data that is represented in a hierarchical or tree-type structure. A tag surrounds each data element. A file record tag might group multiple entries.

File layout definitions tie the identifier along with parent and child relationships to the file record and file field.

There is no PeopleSoft EDI Manager equivalent for this format.

The following table describes the properties of XML format files:

Considerations for XML Format Files

Consider the following when working with XML format files:

• The XML input file must contain at least the fields that are specified in the file layout definition that you’re using.

  If the file is missing any fields, the input rowset doesn't contain any data.

• If the XML input file contains extra fields that aren’t specified in the file layout definition, the ReadRowset method ignores the extra fields.

• When you insert a record into a file layout, fields of the Long type are converted to the Character type, with a length of 0.

  You must set the field length to a number greater than 0 before you can save the file layout.

• The system automatically pads the decimal places with zeros when amounts do not have values beyond the decimal point.

  For example, 100 is automatically written as 100.00.

Constructing File Layouts

This section discusses how to:

• Create a new file layout definition.

• Add file records and file fields.

• Reorder file layout nodes.

• Name file layouts, records, and fields.

• Apply field formats.

Creating a New File Layout Definition

In PeopleSoft Application Designer, select File, New, File Layout.

A new file layout contains only one element—the root node, to which file records are attached. The default root node name is NEW FILE.

Save the new file layout definition. You’re prompted to name the file layout, which also becomes the name of the root node.

Note. The default file layout format is FIXED.

See Also

Specifying File Layout Properties

Adding File Records and File Fields

You can add file records and file fields to your file layout in two ways:

• Base them on existing record and field definitions in the database.

• Construct them directly in the file layout, without reference to any database records or fields.

  A file record that you construct in this way is added as a segment, but the result is an ordinary file record.

You can use both methods in any combination when creating file records and file fields in a file layout definition. Each file record and file field is generically referred to as a node.

Important! Regardless of the method that you use, file records and file fields exist only as definitions within a file layout and have no connection with any database record or field. Even with file records based on records in the database, a change to the database record definition is not reflected in the file layout.

Using a Segment Instead of a Record

Suppose that in the file that is provided to you, some of the file records contain new data and must be inserted, while others contain data that updates existing data. You can add a segment with a single field (like AUDIT_ACTION) that indicates whether the file record is new or changed. When you process the file, you can use PeopleCode to look at this field and, based on its value, perform the appropriate action.

As another example, suppose that you want to include two fields from the PERSONAL_DATA table in the file, but not all of the other fields. You have two choices: insert the PERSONAL_DATA table and manually delete all of the unwanted fields, or insert a segment, name it PERSONAL_DATA, and then insert the two fields that you want.

Using Segments in Data Interchanges

If you’re creating a file layout for a data interchange, you can use segments, but each file record must correspond to a record with the same name in the PeopleSoft database, and its file fields must have the same names and data formats as the database record’s fields. The file record may contain a subset of the fields in the corresponding database record. It may also contain a subset or a superset of the fields that are provided in the corresponding file data.

Adding File Records

To add a file record to the file layout definition, use one of the following methods:

• Drag and drop a database record.

  Drag a record definition from the Project window into the file layout’s Definition window, dropping it on the root node or on any existing file record or file field. The new file record appears at the same level as the node that you dropped it on, following all of the other file records at that level. All of the record’s constituent fields are inserted, as well.

• Insert a database record.

  With the root node or a file record highlighted, select Insert, Record. When you select a record, the new file record appears following the highlighted file record, at the same level. All of the record’s constituent fields are automatically inserted, as well.

• Insert a segment.

  With the root node or a file record highlighted, select Insert, Segment, and enter a file record name. When you click OK, the new file record appears following the highlighted file record, at the same level.

• Insert a child segment.

  With a file record highlighted, select Insert, ChildSegment, and enter a file record name. When you click OK, the new file record appears one level below the highlighted file record, before any others at that level.

Note. When you add a file record at the root level, it appears immediately below the root node, before all of the other file records.

Adding File Fields

To add a file field to the file layout definition, use one of the following methods:

• Drag and drop a database field.

  Drag a field definition from the Project window into the file layout’s Definition window, dropping it on any existing file record or file field. Confirm the field name or enter a different one, and click OK. The new file field appears following the node that you dropped it on.

• Insert a database field.

  With a file record or file field highlighted, select Insert, Database Field. Confirm the field name or enter a different one, and click OK. The new file field appears following the highlighted node.

• Insert a file field.

  With a file record or file field highlighted, select Insert, FileField, and enter a file field name. When you click OK, the new file field appears following the highlighted node.

Note. Each file field must have a unique name within its parent file record, but file fields in different file records can have the same name.

Reordering File Layout Nodes

The file layout definition provides a set of directional arrow buttons in the toolbar, which you can use to reposition any file record within the hierarchy of the file layout or any file field within its parent file record.

The up and down arrows don’t change the level of the selected item, just its order among other items at that level. The right and left arrows move the selected item lower and higher in the file layout hierarchy.

Note. When you reposition a file record in the file layout, its child records are also repositioned, and their child records are repositioned, and so on.

Naming File Layouts, Records, and Fields

File layout names can be 30 characters in length, and file record and file field names can be 15 characters in length. All names should follow PeopleSoft naming standards.

Each file record within a file layout must have a unique name, but one file record can have the same name as the file layout. Each file field within a given file record must have a unique name, but file fields in different file records can have the same name.

Using WriteRecord, ReadRowset, and WriteRowset

If you use the WriteRecord, ReadRowset, or WriteRowset file layout methods for writing to or reading from records, the application record and the file record must have the same name, and the application record fields and the file fields must have the same names. These methods write only to like-named records and like-named fields within a given record. If you rename a record or a field after you use it to create a file layout definition, you must rename the file record or file field to the exact same name.

In a file layout definition containing more than one record, records and fields that aren’t like-named are ignored. Like-named records don’t have to contain all of the same fields, and like-named fields don’t have to be the same length. Like-named fields should, however, be of the same type.

See Also

Naming Record Definitions

Applying Field Formats

You must consider several issues when specifying field formats.

Outputting Numbers to FIXED Files

When you write numeric data to a FIXED format flat file, all numbers are right-justified in the file field. Numbers with decimal places specified are written with zeros padding the unused decimal places.

For example, a sequence of records with numbers of varying precision is written this way:

001    53.2700BUY
002  2174.0933SELL
003   108.0000SELL

Date, Time, and Datetime Field Considerations

In accordance with the International Organization for Standardization (ISO) 8601 standards, the field lengths for date, time, and datetime fields are fixed in the file layout, regardless of the file format:

• Date fields have a fixed length of 10.

• Time fields have a fixed length of 20.

• Datetime fields have a fixed length of 31.

Considerations for Using Dates with the ReadRowset Method

Single digits in dates in the form MMDDYY or MMDDYYYY must be padded with zeros. That is, if the date in the data is February 3, 2002, the format must be:

• 02/03/2002

• 02/03/02

The format 2/3/02 is not valid.

When using the date format DDMMYY, single digits should also be padded with zeros and the month value should be in all capitals. For example, if the date in the data is February 2, 2003, the format must be 02-FEB-03; 2-Feb-03 is not valid.

See Also

File Layout Error Processing

Specifying File Layout, Record, and Field Properties

This section discusses how to:

• Specify file layout properties.

• Specify file record properties.

• Specify file field properties.

Note. Some properties are available only for a specific file layout format. For example, a file definition tag is available only for an XML format file. When a property is available only for a particular format, the format is noted in parentheses after the name of the property (such as File Definition Tag [XML]).

Specifying File Layout Properties

The File Layout Definition Properties dialog box contains all of the information that is stored at the file layout (root) level.

Access the dialog box by using one of the following methods:

• Select File, Object Properties.

• Press Alt+Enter.

• Double-click the topmost (root) node of a file layout definition.

• Right-click an open file layout, and select Data Object Properties.

The General tab of the dialog box contains description information for the file layout. The Use tab contains specific information for the file layout.

File Layout Format	Displays the type of file layout. Values are FIXED, CSV, and XML.
Definition Qualifier	Select a qualifier to surround each field in the file record in this layout. You can override this value at the file record and file field levels. This field is active when the file layout format is CSV.
Definition Delimiter	Select the default delimiter between each field in the file record in this layout. You can override this value at the file record level. The default value is Comma. If you specify Other, a blank field appears so that you can enter a delimiter. This field is active when the file layout format is CSV.
File Definition Tag	Enter the XML tag name that is associated with this layout (or transaction). This tag can be 30 characters in length. This tag must be unique in the file layout. This field is active when the file layout format is XML.
Buffer Size	Displays the size of the input buffer that is used at runtime. This field is active when the file layout format is XML. Note. Do not edit this value directly.
Imply Decimal Place	Select to automatically add a decimal place based on the decimal value that is specified in the field definition when importing a file into a PeopleSoft database and no decimal place is specified for numeric values in the file. If you select this option, make sure that the length that you specify for the Number field includes the decimal point, even though it is not visible. In this case, the decimal point is counted as a space. This field is active when the file layout format is FIXED.
Excel Format	Select to parse the field by using Microsoft Excel CSV specifications. This field is active when the file layout format is CSV.
Qualifier Optional	Select to parse the field based on delimiters. When a qualifier exists, the field is parsed by using a closing matched qualifier. All characters are ignored before and after the qualifier. This field is active when the file layout format is CSV. If you do not select this check box, the field must have qualifier pairs. All characters are ignored before and after the qualifier.
Convert Tabs to Spaces	Select to convert all tabs within the field to spaces. If you do not select this option, all tabs are ignored. This field is active when the file layout format is CSV. Note. If the Strip White Space option is also selected, the system removes trailing and leading tabs.
Interpret Backslashes	Select to interpret backslashes within field data as escape characters. The following characters have special meanings: \t represents a tab. \n represents a new line. If you do not select this check box, backslashes are considered regular text data. This field is active when the file layout format is CSV.
Strip White Space	Select to remove all leading and trailing white space within field data from the output data. This field is active when the file layout format is CSV. Note. If you select this option, trailing and leading tabs are also removed, regardless of whether you have selected the Convert Tabs to Spaces option.
Double Quote	Select to interpret two double quotes (" "), as one double quote (") in output data. For example, if you enable this option, abc""def" is interpreted as abc"def. This field is active when the file layout format is CSV.
Backslash Quote	Select to interpret backslash quotes (\") as one double quote (") in output data. For example, if you enable this option, "abc\"def" is interpreted as abc"def. This field is active when the file layout format is CSV.

See Also

Understanding File Layouts

Specifying File Record Properties

The File Layout Segment Properties dialog box contains information that is stored at the file record level.

Access the dialog box by using one of the following methods:

• Double-click the file record node.

• Right-click the file record node, and select Selected Node Properties.

File Record Name	Enter a file record name that is associated with this file record. This name is used when accessing the file record from PeopleCode. Every file record in a file layout must have a unique name.
ID Seq No. (CSV)(identification sequence number)	Enter a sequence number for the field that contains the file record ID.
Max Rec Length(maximum record length)	Displays the default maximum length of the combined field sizes of the record. This value is automatically updated. Warning! Any inbound or outbound data is truncated beyond this value.
File Record ID	Enter a number to uniquely identify the file record in the file layout. You can use this number in processing the file. This number is automatically written to the file if you use the WriteRecord or WriteRowset methods and if the file type is FIXED or CSV.
ID Start Position (FIXED)	Enter the column or starting position in the file record where the file record ID starts.
ID Length (FIXED, CSV)	Displays the length of the file record ID. This number is automatically generated when you enter the file record ID. Note. This value shouldn’t be edited directly.
Default Qualifier (CSV)	Enter a qualifier that is used for the file record ID and as the default for fields when no field qualifier is specified. This value overrides the definition qualifier that is specified in the File Layout Definition Properties dialog box. When you first create a file layout, this property is blank.
Field Delimiter (CSV)	Enter a delimiter that is used for all fields in the file record. This overwrites the definition delimiter that is specified on the File Layout Definition Properties dialog box.
Record Tag (XML)	Enter an XML tag name for this file record. The default value is the file record name. Note. Although each record name in a file layout must be unique, record tags do not have to be unique.
Record Description	Enter a description of the record for documentation purposes only.

Specifying File Layout Field Properties

The File Layout Field Properties dialog box contains information that is stored at the file field level.

Access the dialog box by using one of the following methods:

• Double-click the file field node.

• Right-click the file field node, and select Selected Node Properties.

Most individual properties are usable by all field types. However, some are specific to a particular field type—for example, the Upper Case check box is applicable for character fields only, while the Date Separator field is applicable for date fields only, and so on. The preceding dialog box shows the properties for a character type of field. However, the following descriptions include all possible properties.

;-

; -

100 8113 Frumman,Wolfgang
101      08/06/1999     000001   219 Going to London office
102                 100 000015 I 08/06/1999
102                 200 000030 I 08/06/1999
102                 300 000009 I 08/06/1999
102                 400 000001 I 08/06/1999
102                 500 000011 I 08/06/1999

Note. Numbers that contain thousand separators (as in "," or ".") are not accepted by PeopleSoft. For example, if you exchange data with Microsoft Excel, you need to disable the "Use 1000 Separator (,)" property.

See Also

Creating Field Definitions

Performing Data Interchanges

This section provides an overview of the import process and discusses how to:

• Preview input data.

• Generate and run the import program.

• Export data.

Understanding the Import Process

To help you troubleshoot and import flat file data, the file layout definition provides a data preview page. It can also generate an Application Engine program with the associated PeopleCode that is necessary to import the data.

Data Import Activities

Importing data by using a file layout requires the following sequence of activities:

1. Provide the import data in a properly formatted flat file.

   Each record in the file must correspond to a record with the same name in the PeopleSoft database, and its fields must have the same names and data formats as the database record’s fields. Each record in the file must end with a new line character.

2. Create a file layout definition to match the record and field structure of the data.

   Insert the appropriate record definitions into the file layout, then reposition the file records and file fields to match the record and field positions in the file.

3. Preview and troubleshoot the input data format and content.

4. Generate the data import Application Engine program and PeopleCode.

5. Run the Application Engine program to import the data.

Field Inclusion and Exclusion

The fields in the data file’s records can be a subset of the database record’s fields—define your file layout with only the file fields that you expect to receive.

The fields in the data file’s records can be a superset of the database record’s fields; you must define your file layout to suppress or ignore the extra fields. For FIXED files, don’t define a field at the corresponding position in the file layout definition. For CSV files, the file layout must have the same number of fields in each record as there are in the corresponding file record—for each field that you don’t want to import, define a field in the file layout at that position that doesn’t correspond to any field in that database record. For XML files, any extra fields are ignored automatically.

Your data file can contain a subset or a superset of the records that are defined in the file layout. Only a file record with a matching file record ID in the file layout is imported.

Record Hierarchy

In theory, you can ignore rowset hierarchy when importing file data, because the PeopleSoft database stores each record independently of the others, and rowsets aren’t used in the import process. However, many records are designed with hierarchical dependencies in mind. The input file might omit inherited field values or order the data records in a way that reflects such dependencies.

If the input file omits inherited field values, make sure that the inheriting fields’ records in the file layout are children of the ones from which they inherit their values, and make the appropriate Field Inheritance settings.

If the records to be imported contain key fields that reflect a rowset hierarchy, they might be in an order in the file that also reflects the hierarchy. Make sure your file layout reflects that hierarchy, as well.

Important! The completed file layout must have exactly one file record at the root level; all other file records must be below that level.

See Also

Reordering File Layout Nodes

Previewing Input Data

In PeopleSoft Application Designer, select File, New, File Layout, Preview.

Initially no information is displayed on the preview tab.

Note. With an XML file, the data can’t be previewed, although you can still generate and run an import program.

To preview the input data:

1. Click Browse and select the import file.

   The name of the import file appears in the Import File text box.

2. Select a file layout segment from the Segment drop-down list box.

   The file layout fields corresponding to the selected segment appear in the list below the segment name. This list comes from the file layout definition, and does not depend on you selecting a valid import file.

3. Click the Refresh button on the toolbar to refresh the preview data.

   At the bottom of the window, a preview of the first five rows of the selected segment in the selected import file appears in the grid.

   You can also select View, Automatically Read Preview Data. While this menu item is selected, the file layout refreshes the preview automatically; when it’s cleared, you need to click the Refresh Preview Data button to see the effects of changes you make to the property settings or data.

   Note. Preview file data is available only for FIXED and CSV file layouts. The preview data appears only if you’ve selected an import file, if the file format matches the format specified for the file layout definition, and if it contains a file record ID that matches the file record ID of the selected segment.

4. Select one of the fields on the Field Name list.

   The properties of the field that you selected appear in the appropriate display fields. These properties are actually the field’s file layout field properties; you can change all field property settings from this view.

   Note. Only the field properties that are appropriate to the file layout format that you specified in the File Layout Definition Properties dialog box are visible in this view; for example, the Start Position and Field Length fields are available only for a FIXED format file layout.

Examining the Input Data for Errors

The following table shows some examples of input data errors:

Generating and Running the Import Program

This part of the process imports the file data.

To generate and run the import program:

1. After you preview the file layout and examine the input data, click the AE button on the toolbar.

   This generates the Application Engine import program with its associated import PeopleCode.

2. Enter a name for the Application Engine program, and click OK.

   The program is automatically saved and is ready to run as soon as its definition appears in PeopleSoft Application Designer.

3. Click the Run Program button on the toolbar.

   The file data is imported into the database.

Exporting Data

The method that you use to export data from the PeopleSoft system depends on the target application’s requirements. To export data to a flat file, you create a file layout definition, then write PeopleCode to transfer the data to a file. The PeopleCode can be initiated from Application Engine or from any event. It should populate text strings, rowset objects, or record objects, and it should apply the WriteRecord or WriteRowset method from the File class to transfer the data to the file by using the file layout definition to position the records and fields as required by the target application.

Note. To generate valid XML files, be sure to use the Close method in the File class when you finish writing to the file.

See Also

File Class

Producing a Sample File Layout

This section describes how to:

• Create the file layout definition.

• Adjust file layout properties.

• Insert a segment and a field.

Creating the File Layout Definition

This example illustrates how to create a file layout that can be used with the QE_ABSENCE_HIST record:

For simplicity, suppose that each row in the FIXED format file has the following structure:

888 A
000 8001 VAC  1981-09-12 1981-09-26 14  .0                     P Y  
888 A
000 8001 VAC  1983-03-02 1983-03-07 5   .0                     P Y  
888 A
000 8001 VAC  1983-08-26 1983-09-10 13  .0                     P Y  
888 A
000 8516 MAT  1986-06-06 1986-08-01 56  .0                     P Y  
888 C
000 8516 SCK  1988-08-06 1988-08-07 1   .0                     P Y  
888 A
000 8516 VAC  1987-07-14 1987-07-28 14  .0                     P Y  
888 A
000 8553 JUR  1990-12-12 1990-12-17 5   .0  Local Jury Duty    P N  
888 A
000 8553 MAT  1992-02-20 1992-10-01 224 .0  Maternity Leave    U N  
888 A
000 8553 MAT  1994-08-19 1995-03-01 194 .0  Maternity          U Y  
888 A
000 8553 PER  1993-04-15 1993-04-19 4   .0                     U N Personal 
Day required
888 C
000 8553 SCK  1987-01-28 1987-01-30 2   .0  Hong Kong Flu      P N  
888 A
000 8553 SCK  1988-08-02 1988-08-03 1   .0  Sick               P N  
888 A
000 8553 SCK  1995-09-12 1995-09-13 1   .0                     P N  
888 C
000 G001 MAT  1991-07-02 1991-09-28 88  .0  3-month Maternity  P Y Maternity will 
be paid as 80% of Claudia's current salary.

000 is the file record ID for ABSENCE_HIST, and each field appears in the same order as in the ABSENCE_HIST database record. 888 is the file record ID for an extra segment called CHANGE_ACTION, containing an AUDIT_ACTION field with the following meanings:

• A: Row inserted.

• C: Row updated, but no key fields changed.

Note. The end of file (EOF) character must be on a separate line and not on a line containing data for any incoming file, regardless of the file type. Each data line needs to be terminated with an end of line (EOL) character, which is different from an EOF.

To create a file layout definition:

1. Use the QE_ABSENCE_HIST record definition as a template for the file layout.

   Create a new file layout, then drag the QE_ABSENCE_HIST record into the open file layout.

2. Save the file layout.

   Save it with a name of ABS_HIST.

   The name of the first node changes from NEW FILE to ABS_HIST.

Adjusting File Layout Properties

In the file layout definition, open the file layout, record, or field properties as appropriate for each step in the following procedure.

To adjust layout, record, and field properties:

1. Change the file layout properties.

   Double-click the topmost node in the file layout, ABS_HIST, to display the file layout properties.

   Enter a short and long description of the file layout you're creating. For this example, we're creating a FIXED file layout, so you don't need to make any changes on the Use tab.

2. Change the file record properties.

   Double-click the QE_ABSENCE_HIST file record to display its properties. Enter a record ID of 000, and a starting position of 1. The ID length is automatically set.

   When you click OK, you see a message asking if you want to increment the start positions for all fields.

   Click Yes. This automatically increments the start position numbers for every field to take the length of the file record ID that you just added into account. If you don't click Yes, you must manually increment the start position for all fields.

   You’ve just created the file record ID for the QE_ABSENCE_HIST record:

   000 8001       VAC  1981-09-12 1981-09-26 14  .0                     P Y  
   000 8001       VAC  1983-03-02 1983-03-07 5   .0                     P Y  

3. Change the file field properties.

   When a record definition is used as a template for a file layout, the default starting position for each field is based on the order in which it appears in the record as well as its length.

   Double-click the QE_EMPLID file field to display its properties.

   The start position is automatically incremented to 4 (because the file record ID is three characters long). However, in the example there’s an extra space between the end of the file record ID and the first field. Therefore, you need to change the start position of this field and all of the fields after this field. This increments the starting position of this field and all fields following this field by 1.

   To do this:

   • Click the up arrow under Propagate to change that number from 0 to 1.

   • Click the button with the arrows pointing right (>>>).

4. (Optional) Adjust other fields.

   The last field (QE_COMMENTS) has a length of 0 because it's based on a Long field type.

   Note. When a Long field is inserted into a file layout, it’s converted to a Character field with a length of 0.

   Because the format for this file layout is FIXED, you have to change the field length of that file field so it’s long enough to accommodate the data that you expect from the file.

   You don’t have to propagate this change because this is the last field in the record.

Inserting a Segment and a Field

Open the file layout definition based on the ABS_HIST record.

To insert a segment and a field in that segment:

1. Insert a segment.

   Insert a segment that is a sibling, (that is, at the same level), as the QE_ABSENCE_HIST record. Insert a segment by selecting Insert Segment from the pop-up menu, or by selecting Insert, Segment. The Insert New Segment dialog box appears.

   This dialog box is identical to the File Layout Segment Properties dialog box. Enter the file record name, file record ID, and ID start position. When you click OK, the segment is inserted.

   Note. If you provide a Record File ID when adding a segment to a file layout that is either CSV or fixed, the system prepends this Record File Id to every row of data that gets written.

2. Insert a file field.

   Insert a file field by selecting Insert FileField from the pop-up menu. The Insert New Field dialog box appears. It’s identical to the File Layout Field Properties dialog box. Enter the field name, start position, and field length; then click OK.

   Note. The start position isn’t automatically set when you add a file field to the file record; for this example the AUDIT_ACTION field requires a start position of 5.

3. Save your work.

   Be sure to save the changes that you've made to the file layout by selecting File, Save, or clicking the save icon in the toolbar.

   Now that you’ve created and saved a file layout, you must use PeopleCode to access the data. File layouts rely solely on PeopleCode as the engine behind the actual data access and movement.

See Also

File Class