Export and Import feature

The Export and Import feature allows you to perform bulk data updates enrichment of items or categories (collectively called "entries") in a collaboration area by using Microsoft Excel.

About this task

You can export selected entries and columns from a collaboration area step, edit the generated Microsoft Excel file, and then import the updated Microsoft Excel file.
  • You can edit the following:
    • Change the values of the attributes
    • Add attribute occurrences
    • Add entries
  • Attributes can be of any attribute type.
  • The format for the category name is Hierarchy Name/Category Primary key (full category path and without any spaces) and the exported Microsoft Excel contains full category path.
  • For secondary spec attribute values, the item must be mapped to any categories required for the attribute path to be valid.
  • If you create a new entry (by specifying a primary key that does not exist) and specify values for the secondary spec attributes, you must ensure that the categories that are associated with those secondary specs are given in the normally hidden columns to the left of the primary key column.
  • If an exported item is mapped to one or more categories, the paths of the categories are exported to cells in the item's row immediately to the left of the primary key attribute column.
  • Some important scenarios for this feature.
    Table 1. List of Export and Import scenarios (collaboration area)
    Scenario Result
    Value of the save_as_draft property is set to true in the common.properties file Saves invalid values in the collaboration area. The errors show up only when you attempt to move the entry to the next step.
    Importing a reserved entry Entry is imported successfully.
    Importing an entry that exists in the source container, but does not exist in the step
    • No new entry is created.
    • An error is logged.
    Invalid attribute value
    • Import job is successful.
    • Displays an error when an entry is saved.
    • An error is logged.
    Updating a read-only attribute
    • Existing value is not changed.
    • An error is logged.
    Invalid category path
    • Import job is successful.
    • Appropriate warning message is logged.
    Importing an existing primary key
    • Import job is successful.
    • Entry is updated successfully.
    Importing updates to existing items, but all the items are reserved by other user
    • Import job is successful.
    • Appropriate message is logged.
    Importing a new primary key value New item is created in the collaboration area step.
    Empty collaboration step Exported file contains a blank template with all the attributes selected.
    • Adding new attributes
    • Removing occurrences
    • Deleting entries
    • Deleting attributes
    • Import job is successful.
    • Adding new attributes - An error message is logged.
    • Removing occurrences, deleting entries, deleting attributes - Not Supported.
    Removing existing category mapping
    • Import job is successful.
    • Removing category mapping is not supported.
    Invalid file name
    • Import job is successful.
    • An error is logged with correct name format suggestion for the worksheet name.
    Click more in the upper-right corner of the interface to see the detailed report for an import job.

Procedure

  1. Browse to Home > <collaboration_step>.
  2. In the multi-edit page, click Export.
  3. Double-click to open the exported data in the Microsoft Excel.
    The exported Microsoft Excel file contains three tabs. The first tab has brief usage instructions. The second tab has attribute values, and the third tab has ranges of cells to define valid values for enumeration and lookup attributes.
    • For a required attribute, the header rows highlight the attribute path and name in the red.
    • First column contains the primary key attribute.
    • The category columns are hidden, by default.
    Attribute type Export details
    Grouping and multi-occurrence Each occurrence is exported to a separate column. To add an occurrence, insert a column immediately to the right of the highest existing occurrence, copy the two header cells to the column, and increase the occurrence number in the attribute path in the header by 1. Ensure that there are no gaps in the sequence of occurrence numbers.
    • You cannot change the value of an existing occurrence to null.
    • If no value is specified in the new column, then the occurrences are not created for such attributes.
    • Multi-occurrence sequence and multi-occurrence relationship attributes are ignored during import.
    Date and Date/Time For import, specify the value in the MM/DD/YYYY HH:MM AM/PM format for both the attributes.
    Linked and Lookup The value is the primary key of the link target or the Lookup table row. Exported only as a primary key. You can specify a new value from the drop-down list in the column. Any invalid values in the Lookup and Linked attributes columns might lead to data corruption.
    Relationship The value is container_name>>primary_key. Here the container_name is the name of the target's container and the primary_key is the primary key of the target.
    Currency Does not contain any currency symbols.
    Binary, Image, Thumbnail On import, only the string value is replaced, and any associated uploaded file is not changed.
    Sequence, String enumeration
    • On import, values of Sequence type for attributes other than the primary key are ignored.
    • For import of primary keys of the Sequence type,
      • If the value matches that of an existing entry the feature associates the values in the row with the existing entry.
      • If the value is -1, a new entry is created with the next key in sequence, and the values given in the row.
      • If the value is null or does not match that of an existing entry, the row is ignored.
    • For a String enumeration, you can specify a new value from the drop-down list in the column.
    Password
    • For export, the password value is exported as "********" (excluding the double quotation marks).
    • For import, any value other than "********" replaces the existing value.
  4. Update the data as required and save the updated Microsoft Excel file.
  5. To bulk enrich the entries, click Import, and select the saved Microsoft Excel file to trigger an import job. Click more in the upper-right of the interface to check the import job status.
  6. Click Refresh to reflect the updates in the multi-edit page.

Results

After a successful import job completion, click more in the upper-right corner of the interface to access the Completed jobs to see the details. To see the summary status click Download Report link. For any technical issues, check the $TOP/logs/scheduler_<hostname>/ipm.log file.