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.

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, PeopleCode can use a file layout as a template to identify and correctly process the records and fields of the file. 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 do not 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, you use text strings to read or write data.
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 does not support PeopleSoft Integration Broker or component interfaces.

Note: Do not create or save files in Microsoft Excel and then use those files for file processing.

File layouts do not have any related-language processing abilities.

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.

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 format is the default for new file layouts.
CSV: Fields are located in the file by their sequence, separated by commas.
XML: Fields are located by the named XML tags that surrounding them, not by their position or sequence within a record.

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 field collections remains.

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

A file layout is a collection of file records, which in turn is 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 are 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:

Property	Description	PeopleSoft EDI Manager Equivalent
File Layout Format	File format (FIXED).	None.
File Record ID	A group of numbers that can be used to identify the file record.	RowID.
ID Start Position	The column starting position of the file record ID.	Treated as a field within each map.
(Record) ID Length	The length of the file record ID.	Treated as a field within each map.
File Record Name	A user-specified name for the file record.	PeopleSoft record name.
File Field	A user-specified name for the file field.	PeopleSoft record field name.
(Field) Start Position	The column starting position of the file field.	Starting position.
Field Length	The length of the file field.	Length of the field.
Field Format	The formatting options for both inbound and outbound field processing.	Based on the field type.

Considerations for FIXED Format Files

Consider the following points 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.
You might overlay fields, with unpredictable results.
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.

In this type of file, each data element is surrounded with a separator, a delimiter, or both. You can use file record IDs to determine the source table and the target table; 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:

Property	Description	EDI Manager Equivalent
File Format	File format (CSV).	None.
File Record ID	A group of numbers that can be used to identify the file record.	RowID.
ID Sequence Number	The sequence number of the field that contains the file record ID.	Treated as a field within each map.
Qualifier	The character that surrounds a field to mark its extent. This character can be set at the file layout, file record, or file field level.	Delimiter.
Delimiter	The character used to separate fields from each other. This character can be set at the file layout or file record level.	Separator.
File Record Name	A user-specified name for the file record.	None.
File Record Field	A user-specified name for the file field.	None.
Field Format	The formatting options for both inbound and outbound field processing.	Based on the field type.

Considerations for CSV Format Files

Consider the following points 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 do not 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:

Item	Microsoft Excel CSV Parsing	Non-Microsoft Excel CSV Parsing
White space	Leading and trailing white space is significant for strings.	Leading and trailing whites paces are ignored, including converted tabs.
Backslash	Not interpreted as special characters except for new lines.	Interpreted.
Quotes inside quoted string	Double quoted.	Escaped.
Tab	Converted to a single space.	Interpreted as tabs and ignored.

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.

PeopleSoft EDI Manager has no equivalent format.

The following table describes the properties of XML format files:

Property	Description
File Format	The XML file format.
File Record ID	The tag name representing the file record.
Field Identifier	The tag name representing the file field.
Field Format	The formatting options for both inbound and outbound field processing.
File Record Name	A user-specified name for the file record.
Field Name	A user-specified name for the file field.

Considerations for XML Format Files

Consider the following points 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 use.
If the file is missing any fields, the input rowset does not contain any data.
If the XML input file contains extra fields that are not specified in the file layout definition, then 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.
When importing XML files, the longest tag name supported is 30 characters. This restriction is only an issue when importing XML files because PeopleSoft Application Designer automatically enforces the limit .

Exporting Data to ISO Standard-Format XML Files

If you need to export data to an ISO standard-format XML file, with respect to thousands separators, decimal separators, and date format for XML format files, you can enable that behavior for the system by adding a custom section and parameter to the application server and/or Process Scheduler configuration file of the appropriate server process.

If the file layout output is generated from PeopleCode embedded in a Peoplesoft page, thte application server configuration file setting is used. If the file layout output is generated by running an Application Engine program through Process Scheduler, the Process Scheduler configuration file is used.

The following procedure assumes a working knowledge of PeopleSoft server administration. See Understanding PSADMIN.

To add the custom File Layout settings:

Open the configuration file of the appropriate domain.
For a Process Scheduler domain, open PSPRCS.CFG, and for the application server domain , open PSAPPSRV.CFG.
Add a new section to the file:[File Layout].
In the [File Layout] section, add the parameter IsoXmlOutput and set it to Y.
For example:
```
[File Layout]
IsoXmlOutput=Y
```
Save the configuration file.
Reboot the Process Scheduler domain or application server domain.
Repeat these steps for additional domains involved.

Once enabled, XML file layout output would follow ISO standards, regardless of any regional personalizations set by the end-user. For example, these ISO standards would appear in the XML output.

no thousands separator and a dot (period) for a decimal separator.
8000.00
dates in the YYYY-MM-DD format.
2014-01-12

Note: Enabling ISO output for XML file layouts affects export behavior only (from a database table to a file). It does not change the existing import behavior (from a file to a database table) of an XML-type file layout. As a result, a file exported with IsoXmlOutput enabled may not necessarily be able to act as the source file for importing data into the system.

Understanding File Layouts

File Layouts

Applying File Layouts to Data Interchanges

Field Formats in a Flat File

FIXED Format Files