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.

Performing Copy and Model After Operations in a Load File

Use the Copy Node and Copy Action columns to create a new node based on a copy of an existing node or a model after (in a hierarchy viewpoint) of an existing node using a request load file. (See Adding a Node by Modeling After an Existing Node for more details about the model after operation.)

Considerations

The Copy Node and Copy Action columns are used in Add or Update operations only. If the Action Code column contains any other operation, the Copy Node and Copy Action columns are ignored.
If the Copy Node column contains a node for which there is already a request item in the load file, the Copy Node and Copy Action columns for that node are ignored.
If the Core.Name property for the node type of a node being added via a copy or model after operation is configured to be calculated and stored (see Calculated and Stored Properties), the name of the new node is automatically calculated.

To perform a copy or model after operation in a load file:

Use the Copy Node column to specify the node to be copied or modeled after.
Use the Copy Action column to specify the type of copy operation. Specify one of these values:
- Properties: Performs a copy operation for the node specified in the Copy Node column.. The copy operation copies defined node and relationship level property values from the specified node and includes them as Update actions for the request item. (This is the default value if you do not specify the type of copy operation.)
- Model After (hierarchy viewpoints only): Performs a model after operation for the node specified in the Copy Node column. The model after operation does the following:
  - Copies parent relationships from the specified node and includes them as Insert actions for the request item
  - Copies defined relationship level property values for each parent and includes them as Update actions for the request item

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-2 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-3.
`Copy Action`	`Copy_Action` `CopyAction` `{Copy Action}`	When the `Copy Node` column contains a node, this column specifies the type of copy operation to perform. Enter one of the following values: `Properties`: Perform a copy operation for the node specified in the `Copy Node` column.. `Model After` (hierarchy viewpoints only): Perform a model after operation for the node specified in the `Copy Node` column. See Performing Copy and Model After Operations in a Load File.
`Copy Node`	`Copy_Node` `CopyNode` `{Copy Node}`	Specifies the name of the node to be copied or modeled after. Use the `Copy Action` column to specify the type of copy operation. See Performing Copy and Model After Operations in a Load File.
`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-3 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.
`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.

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.