1. Administering and Working with Oracle Enterprise Data Management Cloud
  2. Data Management
  3. Making Changes Using Requests
  4. Making Changes Using a Load File
  5. Request Load File Format

Request Load File Format

This topic describes the format of Microsoft Excel spreadsheets used to load request items.

Considerations

  • Each worksheet in a spreadsheet file corresponds to a viewpoint.

  • You can load multiple worksheets to multiple viewpoints.

  • Worksheets (and their corresponding viewpoints) must use labels with 30 or fewer characters.

    Note:

    If a viewpoint name is longer than 30 characters, you'll need to create a label which will be used for the worksheet when loading request items to a viewpoint. See Inspecting a Viewpoint for information on using a label.

  • Spreadsheets can contain formulas to derive property values for the standard columns. Spreadsheet formulas can also be used for application-specific properties (FCGL, PLN) and custom properties. Property cells with formula values that equal blank are skipped. Property cells with formula values that equal <clear> or <blank>.

  • Worksheets are processed in left to right order.

  • Duplicate rows in the import file are not processed during the load.

  • Dates and timestamps for supported locales must be in one of these Java date format patterns:

    • Short

    • Default

    • Long

    • Medium

    Note:

    For information on Java date and time formats, see Using Predefined Formats.

  • If you have more than 10,000 nodes, create multiple load files each containing less than 10,000 nodes.

  • For nodes that are added or inserted in a hierarchy viewpoint where the parent is not known, use the <Unknown> keyword in the Parent column to have the value of the parent calculated and stored for each request item. An administrator must have defined an expression to calculate the parent value. See Calculating and Storing the Parent of a Node.

  • To calculate the names of nodes that are being added or inserted, leave the name column blank or use the <cn> or <cn ####> keywords. The node must be set up to calculate the name value. See Calculating and Storing the Name of a Node.

Considerations for Columns

  • The spreadsheet must have column headers that match either the reserved column names or the names of properties, see Reserved Column Names.

    Note:

    You cannot load a sheet if the Name column is missing.

  • Column headers for properties must consist of the property name or the property's custom displayed label if one has been set up (this option is set in the Viewpoint inspector Properties tab). The property name cannot include the namespace. For example, Core.Description is not valid.
  • If the load file has a column for the Alternate Name property:
    • If the node type being loaded to contains the Alternate Name property, when loading the file the following operations are performed:
      • If a row contains a node name, the alternate name is loaded in the Alternate Name column.
      • If a row does not contain a node name, the Alternate Name is used to find a matching node in the viewpoint with the same Alternate Name property value.

    • If the node type being loaded to does not contain the Alternate Name property, the column is ignored.

  • The columns can be in any order.
  • If a column's header does not match any of the reserved column names or property names, the column is ignored. This means that the spreadsheet can include comments that will not be loaded.

Processing Order of Rows

When loading records from a request file, the load process sorts the file contents so that parent nodes are added to a viewpoint before child nodes. This prevents validation errors if a child node's row precedes its parent node's row in the load file.

For example, this load file contains a parent row, Analytics, that is preceded in the file by two child rows, Emily and Jack, that both have Analytics as their parent:


A spreadsheet with rows for child nodes preceding the row for their top node.

When this file is loaded, the load process sorts the contents so that Analytics is added first, then Emily, Jack, and Mary.

<Blank> and <Clear> Processing Versus Empty Fields

Entering the <blank> or <clear> keywords and leaving cells empty in the request load file are handled differently when processing a request file:

  • The <clear> keyword clears the existing values from a property.
  • The <blank> keyword sets a defined value of blank for the property.
  • Empty cells in the file are ignored.

The <blank> and <clear> keywords and empty cells are supported for all data types. However, for list data types, blank entries within a list are not supported. That is, the list itself can be empty, but an entry within the list cannot.

Some examples:

  • List value: '<blank>' is supported. Note that importing a blank for a list will override any existing defined, inherited, or default values in the list.
  • List value: 'A,B,C' is supported.
  • List value: 'A,,C' is not supported.

Reserved Column Names

Considerations

  • If a property has the same name as that of a reserved column, you can use an alternate column name. See the supported alternate names below.

  • If the spreadsheet is being loaded into a viewpoint for a list, columns and actions for hierarchies are ignored. For example, Move and Insert actions would be ignored in a list viewpoint.

Table 10-1 Standard Columns

Column Name Alternative Column Names Description
Action Code

Note:

If the load file does not contain a column with this name or one of the alternative names for the Action Code column, then the action code for all of the request items in the file is set to Update.
  • Action_Code
  • ActionCode
  • {Action Code}
 Specifies how the node will be processed. For example, there are action codes for adding, moving, and deleting nodes. For information on the action codes, see Table 10-2.
Description   A description of the node.
Name   The node name. The combination of the node name and node type must be unique.

This column is required.
New Name
  • New_Name
  • NewName
  • {New Name}
 If the node is being renamed, this cell contains the new node name.
Node Type   The node's node type.

If the viewpoint includes only one node type, the column is optional. If the Node Type cell is empty, the load process uses that node type; however, if the viewpoint contains multiple node types, you must specify a node type in each row.

Old Parent Name
  • Old_Parent_Name
  • OldParentName
  • {Old Parent Name}
 If the node is being moved under a different parent, this cell contains the name of the old parent node.

If the node being moved exists under only one parent, then this cell can be empty. If the node exists under multiple parent nodes, you must specify the name of the parent node from which the node should be removed.
Old Parent Node Type
  • Old_Parent_Type
  • OldParentType
  • {Old Parent Type}
 If the node is being moved under a different parent, this cell contains the name of the old parent node's node type.

If the node being moved exists under only one parent, then this cell can be empty. If the node exists under multiple parent nodes, you must specify the node type of the parent node from which the node should be removed.
Parent   The name of the parent node, if any. If this cell is empty, the node is imported as a top node.

Note:

Use the <Unknown> keyword in the parent column to calculate and store the value for the parent of the request item.
Parent Node Type   The node type of the parent node, if any.
Sibling  

The name of a sibling node under the specified parent that the node should be placed after. Or, if the node being ordered is to be the first sibling under the parent, enter <First>.

Use with the Reorder, Add, Insert, and Move actions.
Sibling Node Type  

The node type of the sibling.

Use with the Reorder, Add, Insert, and Move actions.

Table 10-2 Action Codes

Action Code Description
Add Creates a new node.
Delete Deletes a node.
<Empty cell>

If the Action Code cell is empty, the row is processed using the Update action code.

Insert Inserts an existing node into a hierarchy under the node specified by the Parent and Parent Node Type cells.
Move Moves an existing node to a new position in a hierarchy, removing the node from its existing position.
Prop_Update

Updates the property value for an existing node only. To perform other actions, use the Update action code.

You can clear the property value for a defined property. Clearing the property value deletes the stored value and returns the value to the default or inherited value. For example, the Prop_Update action below clears the Description value.


clear property example
Rename Renames an existing node.
Reorder

Reorders a node in a hierarchy viewpoint.

You can only reorder nodes in a viewpoint that uses a hierarchy set that allows reordering for that type of node (parent or bottom node). For more information, see Reordering a Node.

Remove Removes the node from the specified parent node.
Update Updates an existing node's property values.

The Update action also performs additional actions if the cells in a row indicate that such actions are required. The following list describes how this works:

  • If the node does not exist, the node is added. If the viewpoint is hierarchical, the node is inserted in the position of the hierarchy specified by the Parent and Parent Node Type cells.
  • If the viewpoint is hierarchical and the node exists but is not under the specified parent, the resulting action depends upon whether the hierarchy set allows shared nodes:
    • If shared nodes are allowed, the node is inserted under the specified parent.
    • If shared nodes are not allowed, the node is moved under the specified parent.

You can update a property to a blank value if the property allows blanks. You must use the <blank> indicator in the cell; an empty cell does not produce the same results.

For example, the update action below sets the Legal Entity property to a blank value. Even though the cell for description is empty, the existing description for EMA does not change.


loading a blank property value

Example 10-1 Example

The following example shows a few nodes being added, updated, and inserted into a hierarchical viewpoint:


The image shows a spreadsheet with rows for adding, updating, and inserting nodes.

The following list describes the example's key points:

  • All of the nodes have a node type of Employees.
  • The Business Unit column indicates that the node type has a property named Business Unit.
  • The second row adds a node named Bill. The Parent and Parent Node Type cells are empty, indicating that this is a top node.
  • The third and fourth rows add child nodes under the node for Bill.
  • The fifth row updates an existing node by setting its Business Unit property to a value of Support.
  • The sixth row inserts an existing node named James under a parent node named Joe.