11. Import/Export Excel

In this section:

  1. Export Excel
  2. Import Excel

RepoPro provides a UI for Import/Export that allows the user to Export and Import data to and from an Excel workbook. For more information on Import and Export, refer: Export and Import.

11.1. Export Excel

The entire information stored in the Repository can be exported in the form of an Excel workbook.

To export data to Excel:

  1. In the main Navigation menu, click Import/Export. The Import/Export Excel screen displays. The Import tab is the default tab.

  2. Click the Export tab to display the Export tab screen.

    Export tab screen

    Fig. 11.1 Export tab screen

  3. Click Export to start the Export process.

    Two sheets in the workbook represent each Asset Type - one sheet with Attribute values and another with Relationship details. The Attributes sheet has one row for each Asset Instance and contains the Parameter values, Taxonomies assigned, Tags assigned for each Instance. The Relationships sheet contains one row for each Relationship, and shows the Source Asset Type, Source Asset Instance name, Source Asset Instance Version name, Source Asset Instance Overview, Relationship Type, Relationship Name, destination Asset Type, Destination Asset Instance name, Destination Asset Instance Version name, Destination Asset Instance Overview. On completion, the user receives a mail with the attachment of the XLXS file and the status of export.

  4. Export ldap parameters

    All the parameter values will be displayed in a separate column renamed as parametername_attributename, where attributes will be based on mapping name provided at asset level.

    Note

    Once an Export is triggered, another Export cannot be triggered until the first one has completed.

11.2. Import Excel

RepoPro validates the entire Excel file uploaded as per the rules described below, and updates the database only if the entire file is valid in all respects. Also, all the updates are made as a single transaction. Once an Import is triggered, another Import cannot be taken up until the first one is complete.

The below general rules apply to the Import Excel feature (for all the three Import options: Full Import, Incremental update and Incremental Insert):

  • The file uploaded should be an Excel workbook with an .xlsx extension

  • When the XLSX file refers to “File” type Parameter with files to be uploaded, the actual files and the XLSX file can be zipped together and uploaded

  • Parameters cannot be added or deleted for an Asset Type through Import Excel

  • Users are allowed to modify only the Parameter values for Asset Instances

  • Relationship definitions cannot be added or deleted through Import Excel

  • Relationship names used in the Excel file must match the Relationship defined in the Repository

  • For non-versionable Asset Types, Version name column must be “NA” in Asset Attribute sheet

  • For versionable Asset Types, version name column must be in the “x.y” format in the Asset Attribute sheet

  • Asset Instance Name, Version Name, and Overview column can have maximum lengths of 100, 10, 10000 characters respectively

  • Versions cannot be added through the Excel Import feature

  • Parameter values in Asset attribute sheet must have valid parameter values, like so:

    • Date type: in DD/MM/YYYY format and Year Range in 1900-2999 range with leap year validation
    • File: a file with the name provided must be uploaded, zipped together with the Excel file
    • List: must be one of the valid list elements applicable for the Parameter
    • Text: must match size restrictions

  • Derived Attribute, Derived Computation and Derived attributes for Asset List values are excluded from the Import procedure.

  • LDAP attribute mapping parameter is included for an import procedure.

  • Taxonomies: provide the list of Taxonomies for the Asset Instance, separated by commas. Ensure that all Taxonomies are assigned. If it is an existing Asset Instance, the assigned Taxonomies prior to Import are over-written with the one provided in the imported Excel file. The Taxonomies assigned to the Asset Instance mentioned in the Excel file are validated against the Asset Type-level settings. Provide the path starting from root and ending with node name to be assigned: e.g., country/India, type/paid. The Taxonomies must match with those defined in the Repository.

  • Tags: provide as a comma separated list not exceeding 50 characters. If the Asset Instance is an existing one, the Tags assigned prior to Import are overwritten by those provided in the imported Excel file.

  • Asset Instances cannot be renamed through Import Excel.

  • Duplicate records are not allowed, both in the Attributes sheet and Relationships sheet.

  • Source Asset Type name, Source Asset Instance name, Source Asset Instance Version name, Target Asset Type name, Target Asset Instance name, Target Asset Instance Version name must have max length of 30,100,10,30,100,10 respectively in the Asset Relationship sheet.

  • Source and Destination cannot be same Asset Instance for Association and Classification Type in Asset Relationship sheet

  • Relationships between Asset Instances can be created through the Excel Import feature by adding a row with the correct details, subject to the below:

    • Destination Asset Instance must not exist in the Repository for Composition or Aggregation Relation.
    • Destination Asset Instance must exist in the Repository for Classification and Association Relations.
    • Source Asset Instance must exist in the Repository. It could be a new Instance added through the Attributes sheet also.
    • If Relationship Type is Composition or Aggregation, the Destination Asset Instance version name must be 1.0.
    • Created column details will be skipped, but import Sheet should have created on column for sheet validation.

There are method-specific rules that apply for Imports. These are discussed below. For general rules, refer: Export and Import.

For Full Import, the below additional rules apply, apart from the general rules:

  • The XLSX file must be in the same template as obtained while exporting the Repository data. Do not change the sheet names, or reorder the sheets in the workbook. Each Asset Type has one sheet for Attributes and one sheet for Relationships in the Excel workbook.
  • The application checks whether the XLSX file needs to update any locked Asset Instance versions. If so, then, it prompts for the user to confirm whether to proceed, and provides two options. When “Force unlock and update” option is selected, then locked instances are automatically unlocked and updated as per the values in the XLSX file. When “Skip locked” is selected, locked Instances are not updated.
  • Asset Instances can be deleted from the Repository through the Excel Import feature by deleting the row for the Asset Instance, except when the Asset Instance is at the source of a Composition or Aggregation Relationship.
  • New Asset Instances can be created by adding a row containing Asset Instance version ID as XXXX in Attributes sheet of corresponding Asset Type Excel file. Create Asset Instance through Excel file is allowed through Relationship sheet of the parent Asset Type if Asset Type is child (destination of a Composition or Aggregation Relationship) - add a new row in the sheet with the new child Instance details containing Asset Instance version id as XXXX for the destination Asset details.
  • When Concurrent Editing Lock is enabled, Full Import takes place by unlocking all those locked instances that have the Force unlock and update option, where any change is made in the value of Overview Description, Parameter values and adding or removing of Relationships of Classification or Association Relationship Types compared with the values in the Repository of the locked Instances. If there is no change in the value of Overview Description, Parameter values and Relationships, updating takes place without unlocking the Instances. When Skip locked option is selected, locked Instances are not updated in the case of any change made in the value of Overview Description, Parameter values and adding or removing of Relationships of Classification or Association Relationship types compared with the values in the Repository of the locked Instances.

For the Incremental Update, the below additional rules apply, apart from the general rules:

  • The XLSX file must be in the same template as obtained while exporting the Repository data, but shall have all the Parameters in Attributes sheet, and Relationships sheet for the Asset Type whose instances are being updated using the Incremental Update. Do not change the sheet names, or reorder the sheets in the workbook The recommended way to get an Excel workbook for Incremental Update is to go: main Navigation menu > Browse > By Asset Type, select all columns in show/hide option from the hamburger menu to display all columns and export (make sure that all Categories are visible to the current user). The exported file is valid for making changes and importing under Incremental Update.
  • The application checks whether the XLSX file needs to update any locked Asset Instance versions. If so, then, it prompts the user to confirm whether to proceed or not, and provides two options: Force unlock and update and Skip locked. Selecting Force unlock and update option unlocks locked Instances automatically and updates them. Selecting Skip locked does not update the locked Instances.
  • New Asset Instances cannot be created through Incremental Update.
  • Import ldap attribute parameters –
    1. LDAP attribute Mapping Parameter Name must be “parametername_attributename1, parametername_attributename2”, mentioned in alphabetical order and count of parameter should be equal to attributes mapped with mapping name.
    2. Value of first alphabetical Attribute value is considered as a search data to update value for LDAP split parameter but validation will be for all the parameter Attribute list present in import excel sheet.

Note

  • LDAP attribute Mapping parameter can be modified, if Category level access is provided at asset level.
  • LDAP attribute mapping parameter cannot be made static.
  • UID is mandatory LDAP attribute for LDAP mapping.
  • Deletion of existing Asset Instances is not possible.
  • Relationships of type Association or Classification can be added or deleted by adding or deleting the corresponding rows in the Excel sheet. Relationships of type Composition or Aggregation cannot be added or deleted through Incremental Update. When Concurrent Editing Lock is enabled, Incremental Update takes place by unlocking all those locked Instances for Force unlock and update option, where any change is made in the value of Overview Description, Parameter values and adding or removing Relationships of Classification or Association Relationship types compared with the values in the repository of the locked instances. If there is no change in the value of Overview Description, Parameter values and Relationships, updating takes place without unlocking the instances. When Skip locked option is selected, locked instances are not updated in case any change is made in the value of Overview Description, Parameter values and adding or removing of Relationships of Classification or Association Relationship types compared with the values in the Repository of the locked instances.

For Incremental Insert the below additional rules apply, apart from the general rules:

  • The XLSX file must be in the same template as obtained while exporting the Repository data, but shall have all the Parameters in the Attributes sheet, and Relationships sheet for the Asset Type whose Instances are being updated using Incremental Insert.

    Warning

    Do not change the sheet names, or reorder the sheets in the workbook.

    The recommended way to get an Excel workbook for Incremental Insert is to go: main Navigation menu > Browse > By Asset Type, select all columns in show/hide option from the hamburger menu to display all columns, and then do the export (make sure that all Categories are visible for the current user). Delete all rows in the sheet except the header by selecting, right-clicking and selecting Delete Rows from the context menu.

  • New Asset Instances containing Asset Instance version ID as XXXX can be created through this option, but create Asset Instance through Excel file is not allowed if Asset Type is a child (destination of a Composition or Aggregation Relationship).

  • Classification or Association Relationship can be added in the Relationship sheet for the newly created Asset Instance in Attribute sheet.

  • Deletion of existing Asset Instances is not possible

To import data from Excel:

  1. In the main Navigation menu, click Import/Export. The Import/Export screen displays. The Import tab displays by default.

    Import tab screen

    Fig. 11.2 Import tab screen

  2. In the Type of Import radio button option, click the type of Import: Full Import, Incremental Update, Imcremental Insert that you wish to carry out.

  3. In the ZIP/XLXS file to Import box, click Import File to upload the file for the import process.

  4. Click Import to start the Import process.

Note

LDAP User Type parameters are not processed on Import with respect to updating parameter values.