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.
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.
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.

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 Request 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.
Use the <Ignore> keyword in the parent column to indicate that the parent should be ignored during a model after operation. The new node is inserted only under the parents of the node being copied.

To perform a copy or model after operation in a request 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
  Note:
  Model After using related viewpoints is not supported during request file uploads. Therefore, you cannot perform model after operations when uploading request files in list viewpoints.

Working with Data Sources in Request Load Files

Best Practice

Although you can add request items from multiple data sources in a single load file, it is a best practice to create a separate load file for each data source. A request item in a load file can only have one data source, so if you load a request file with a node with the same name from multiple data sources only the first data source in the file is retained. Creating separate load files for each data source enables you to run matching for that node for all data sources.

Data Source Column

The request items in the load file are updated with the data source that you specify in the Data Source column (see Understanding Data Sources). This enables them to be linked or matched and merged to existing nodes in the viewpoint (see About Node Links and Matching and Merging Request Items).

Note:

The data source must be enabled in order for the request items in the file to be updated.

Considerations

You can use the data source Code or Name to identify the data source in the load file. It is a best practice to use the data source code, as the name may change over time.
You can load nodes from registered data sources only if the load file was originally generated from that registered data source (for example, reloading a generated subscription request file). The rows in the load file must contain the generated Source Node ID and Source Node Type from the registered data sources in order to be loaded.
If the request item already references a different data source than the one in the row, the row is skipped.

Reserved Column Names

Considerations

If a property has the same name as that of a reserved column, you can use an alternate column name. For example, if you have a property named Data Source, you can use an alternate name such as Data_Source, DataSource, or {Data Source}.
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	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`.	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`	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 Request Load File.
`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 Request Load File.
`Data Source`	Specifies the data source for a node. You can use the data source Code or Name. However, it is a best practice to use the data source code, as the name may change over time.
`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`	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`	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`	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.
`Source Node ID`	The ID of the source node coming from a registered data source. This column should only be used for request file rows generated by a subscription, from compare results, or from copying nodes across viewpoints.
`Source Node Name`	The name of the source node coming from a registered data source. This column should only be used for request file rows generated by a subscription, from compare results, or from copying nodes across viewpoints.
`Source Node Type`	The ID of the source node type coming from a registered data source. This column should only be used for request file rows generated by a subscription, from compare results, or from copying nodes across viewpoints.

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.