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.
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.
Note: The Application Engine program that the system automatically generates should be viewed as containing sample PeopleCode that you need to review and modify, especially when a single file layout involves multiple records.
Data Import Activities
Importing data by using a file layout requires the following sequence of activities:
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 fields in the database record. Each record in the file must end with a new line character.
Create a file layout definition to match the record and field structure of the data.
Insert the appropriate record definitions into the file layout, and then reposition the file records and file fields to match the record and field positions in the file.
Preview and troubleshoot the input data format and content.
Generate the data import Application Engine program and PeopleCode.
Run the Application Engine program to import the data.
Field Inclusion and Exclusion
The fields in the records of the data file can be a subset of the fields in the database record. Define your file layout with only the file fields that you expect to receive.
The fields in the records of the data file can be a superset of the fields in the database record; you must define your file layout to suppress or ignore the extra fields. For FIXED files, do not 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 do not want to import, define a field in the file layout at that position that does not correspond to any field in that database record. For XML files, the system automatically ignores any extra fields.
Your data file can contain a subset or a superset of the records that are defined in the file layout. The system imports only a file record with a matching file record ID in the file layout.
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 the import process does not use rowsets. 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 records in the file layout of the inheriting fields are children of the ones from which they inherit their values and set up Field Inheritance appropriately.
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.
In PeopleSoft Application Designer, select
Image: File layout preview
This example illustrates the fields and controls on the File layout preview. You can find definitions for the fields and controls later on this page.
The Preview tab displays no information initially.
Note: With an XML file, you cannot preview the data, although you can generate and run an import program.
To preview input data:
Click Browse and select the import file.
The name of the import file appears in the Default File Name text box.
Select a file layout segment from the Segment Name 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 it does not depend on your selecting a valid import file.
Click the Refresh button on the toolbar to refresh the preview data.
At the bottom of the window, the first five rows of the selected segment in the selected import file are available for preview in the grid.
You can also select While this menu item is selected, the file layout refreshes the preview automatically; when it is 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 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.
Select one of the fields in the Field Name list box.
The properties of the field that you select appear in the appropriate display fields. These properties are actually the file layout field properties of the field; 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 examples of input data errors:
|
Symptom |
Possible Reason |
Solution |
|---|---|---|
|
The preview grid does not appear. |
The input file format does not match the file layout format that you specified in the File Layout Definition Properties dialog box. A CSV file does not use the definition qualifier you specified in the File Layout Definition Properties dialog box. |
Change the file layout format to match the input file format. Change the definition qualifier to match the one used in the input file. |
|
Only the first column of the preview grid is populated. |
A CSV file does not use the definition delimiter you specified in the File Layout Definition Properties dialog box. |
Change the definition delimiter to match the one used in the input file. |
|
The preview grid appears for some records but not for others. |
The record IDs of the file for the missing records do not match the file record IDs specified in the File Layout Segment Properties dialog box. |
Specify file record IDs that match the input file records. |
|
Data for a field appears truncated in the preview grid. |
With a FIXED file, the field length that you specified in the File Layout Field Properties dialog box is too short to accommodate the field data. |
Increase the field length to accommodate the input data. |
|
A field appears to start in the middle of the data. |
With a FIXED file, the start position that you specified in the File Layout Field Properties dialog box is too great to include the start of the field data. |
Decrease the start position and adjust the field length to match the input data start position and length. |
This part of the process imports the file data.
To generate and run the import program:
After you preview the file layout and review the input data, click the AE button on the toolbar.
The system generates the Application Engine import program with its associated import PeopleCode.
Enter a name for the Application Engine program and click OK.
The system automatically saves the program, which is ready to run as soon as its definition appears in PeopleSoft Application Designer.
Click the Run Program button on the toolbar.
The database imports the file data.
The method that you use to export data from the PeopleSoft system depends on the requirements of the target application. To export data to a flat file, you create a file layout definition and then write PeopleCode to transfer the data to a file. You can initiate the PeopleCode from the 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.
Important! As is the case with imports, the completed file layout that you use to export the data must have exactly one file record at the root level; all other file records must be below that level.