Excel List of Values

LOV values in existing LOV objects can be maintained with the Excel List of Values format. LOV values in preexisting LOV objects can be imported and exported. Specifically, during import, this format allows the addition of new LOV values, modification of existing LOV values, and deletion of current LOV values.

Note: The Excel List of Values format is not a comprehensive LOV migration tool since creation of new LOVs is limited. For details, refer to the Inbound Data section below.

When exporting using the Excel List of Values format, data is exported as shown below, and includes a header row and four columns. When using Excel List of Values for importing, the same headers and columns in this same order are required in the Excel file.

Format Availability

Excel List of Values format is available for selection in:

• IIEP - refer to Creating an Inbound Integration Endpoint

• Import Manager - refer to Creating a Data Import

• Export Manager - refer to Creating a Data Export

Mapping

When the Excel file includes only the expected columns, no mapping is required. This format only supports LOVs and only processes the predefined elements in the file.

Manually selecting the Excel List of Values format for a file with additional columns displays the following message: Conversion Error: Not a valid Excel List of Values file.

Considerations

• For optimal update and deletion functionality during import, the LOV object must have the following parameter settings:

  • To ensure unique IDs, set the 'Use Ids on values' parameter to 'Yes'.

  • To prevent users from adding values outside of System Setup, set the 'Allow Users to Add Values' parameter to 'No'.

  Additionally, to avoid import / update errors on existing values, it is recommended to set the 'Value-ID Pattern' parameter to '[id]'.

  Refer to the Creating an LOV topic and the Adding IDs to Existing Values in LOV topic, both in the System Setup documentation.

• It is recommended to use the newest supportable version of Excel. Using an older Excel version can result in file errors during processing. For more information on supported versions of Excel, refer to the current 'Platform and Software Support' topic in the System Update and Patch Notes documentation.

• When the LOV value is dimension dependent, the values are exported and imported from the context selected during the export and import process. This means that for a dimension dependent LOV with an empty value in the selected context and a non-empty value in other contexts, the empty value is exported or imported. Since the context is not included in the Excel List of Values file, set the intended context while configuring the export or import tool.

  Important: Exports run on the selected context, which means that to output all the values for a dimension dependent LOV, an export needs to be run for each context.

• The Excel List of Values format is limited to 100 million characters for the <LOV Value> column. If the import file exceeds this limit, the process is aborted.

Outbound Data

For a successful import of LOV value data into an existing LOV in STEP, you must first export an Excel file using the Excel List of Values format.

To export using the Excel List of Values format:

1. In the Export Manager, on the 'Select Objects' step, set the 'Export' parameter to 'List of Values' and click the 'Add Objects' link to choose the LOV.

   

2. On the 'Select Format' step, select 'Excel List of Values'.

   

3. Click the Finish button to generate the export file. For more information, refer to the Running a Data Export topic.

   

   Note: If the exported LOV does not have Value IDs, the <LOV Value ID> column is blank.

4. Edit the <LOV Value ID> and/or <LOV Value> as required to be used for import.

Inbound Data

To successfully import values in an existing LOV as identified by the <LOV ID> in the Excel file:

1. In the Import Manager or IIEP, on the 'Select Data Source' step, select the edited file with the required headers and columns of data.

2. On the 'Select Format' step, the 'Excel List of Values' format is automatically selected if the file has the required columns.

   

3. Set the ‘Replace List of Values’ parameter based on the desired updates as defined in the following sections. On the System Setup tab, review the LOV's 'Use Ids on values' metadata attributes as shown in the images within each of the following sections:

   • LOV 'Use Ids on values' parameter set to 'Yes'

   • LOV 'Use Ids on values' parameter set to 'No'

4. Complete additional wizard steps and click Finish to save and start the import, as defined in the Running a Data Import topic.

LOV 'Use Ids on values' parameter set to 'Yes'

An export of this <LOV ID> generates an Excel file with values for <LOV Value ID>.

Note: An <LOV Value ID> must be unique within the applicable <LOV ID>. It is recommended to set the 'Use Ids on values' parameter to 'Yes' and to set the 'Value-ID Pattern' parameter to '[id]' (as shown in the image below) to ensure unique <LOV Value ID> entries.

• Replace List of Values parameter set to No results in:

  • Appended Values - When the Excel file includes an <LOV Value ID> that does not exist in the LOV, the <LOV Value ID> and <LOV Value> are added to the LOV, including when the <LOV Value> is blank.

  • Updated Values - When the Excel file includes an <LOV Value ID> that does exist in the LOV, the <LOV Value> is changed for the <LOV Value ID> in the LOV, including when the <LOV Value> is blank.

    • Attempting an update for an <LOV Value ID> that is used in an object reports BGP Execution Report message: Unable to replace used values based on ID in listofvalues with ID 'LOV_ID'.

    • When the Excel file includes a duplicate <LOV Value ID>, the last <LOV Value> in the file is used to update the relevant <LOV Value ID>.

• Replace List of Values parameter set to Yes results in:

  • Appended Values - When the Excel file includes an <LOV Value> but does not include an <LOV Value ID>, all existing values are deleted, and the <LOV Value> is added with an auto-generated <LOV Value ID>.

    When the Excel file includes a unique <LOV Value ID> that does not exist in the LOV, all existing values are deleted, and the <LOV Value> is added with the specified <LOV Value ID>.

  • Updated Values - When the Excel file includes an <LOV Value ID> that does exist in the LOV, the <LOV Value> is updated for the <LOV Value ID>.

  Note: Blank <LOV Value> entries are ignored.

LOV 'Use Ids on values' parameter set to 'No'

An export of this <LOV ID> generates an Excel file without values for <LOV Value ID>.

• Replace List of Values parameter has no effect.

  • Every <LOV Value> in the Excel file is appended to the LOV.

  • No deletes or updates are allowed for existing values.