Excel Format for Creating BP Records

The following applies to all BPs where you can create records in bulk through Excel import.

Note:

  • The Request for Bid (RFB) BP does not support bulk creation of records through CSV.
  • The following does not apply to managers such as Space Manager, Portfolio Manager, Configurable Managers, Code and Records-based Configurable Managers, and Code-based Configurable Managers support record creation through Excel import.
  • To download the Excel template, the integration interface must be defined for the BP record. If the BP record does not have a defined integration interface, the import fails and no BP records or line items are created. In that case, the system displays the following error message: Import could not be completed. There are errors in this template file. These errors can be seen in the Import Data Template worksheet. Do you want to download the file and fix the errors? Click 'Yes' to download the file and 'No' to upload a different template file.

To create BP records in bulk through Excel:

  1. Go to your project/shell tab, and switch to User mode.
  2. In the left Navigator, select Logs , and select the applicable BP sub-node.
  3. From the toolbar, select Actions, and select Export Microsoft Excel Template.

    This option is available only to users with View permission for the log.

Excel Template File Name Format

When you export a template file, the exported file name uses the following format: [BPName]_Template

Note:

The file name always uses the source string without spaces.

For example, if the BP name is Daily Reports, the exported template file name is DailyReports_Template.

Excel Template File Worksheets

The exported template file contains the following worksheets:

  • Instructions worksheet
  • Imported Data Template worksheet
  • <Other User-Defined> worksheet

Instructions Worksheet

This worksheet is generated automatically as the first tab of the template file. You cannot change the worksheet name. This worksheet contains information about entering BP record and line item data. The content of the Instructions worksheet depends on the BP type.

Imported Data Template Worksheet

This worksheet is also generated automatically as the second worksheet in the template file. You cannot change the worksheet name. This worksheet contains the Unifier data required to create BP records and line items.

Note:

The name of this worksheet is translated based on the user's language preference.

The top section of this worksheet depends on the BP type.

  • For Cost BPs of the Summary Payment Application SOV type, the Enter date below section includes an additional row for Cost Allocation. In addition to the H and D rows, the CA row also appears. This row uses the asterisk symbol (*) for all required cost allocation fields.
  • For Simple BPs, the worksheet displays the Enter date below section and an H row that contains the column headings.
  • For Line Item BPs, the first few rows list all line item tabs. The first line contains the static informational text, “Line Item Tabs along with the respective detail form fields.” The tabs appear one below another. Hidden tabs also appear, similar to CSV template files. The “Tab Name” is the first column, followed by the Detail Form fields. All required fields in the Line Item tab are marked with an asterisk symbol (*).

<Other User-Defined> worksheet

You can insert additional worksheets for reference. However, Unifier does not process the content of these worksheets during import.

If the BP includes a work package picker, the worksheet includes a Work Package field with a drop-down list of values. Work package names are case-sensitive.

Entering Data in the Excel Template File

To enter data in the Excel template file, use the Import Data Template option to create records or records with line items.

Note:

Review the information on the Instructions worksheet to reduce import errors.

When you enter data, you can:

  • Enter data in all fields.
  • Copy column values from another sheet. That is, you can use native Excel features when you enter data.
  • Use Excel formulas to calculate values in columns.
  • Enter UTF-8 character strings in String fields.

For all data types, Unifier processes only the data, not the format, during import.

For example, if you enter -999.99 and format the cell as (999.99), Excel displays (999.99). When Unifier processes the data, it uses -999.99, not (999.99).

Creating BP Records by Importing an Excel Template

To create BP Records by importing an Excel template:

  1. From the toolbar, select Actions, select Import, and then select From Microsoft Excel File Template.
  2. Follow the prompts.

If you can create new BP records, the Import option further displays the following options:

  • Data From CSV File
  • Data From Microsoft Excel File

Uploading Excel Files

When you upload an Excel file in the Upload Microsoft Excel File window (similar to the CSV window), use the Select File option to select the Excel file, and then select Next to continue with the upload. See below for information about adding attachments.

Validating Microsoft Excel File Content

During the import process, Unifier validates content at multiple levels:

  • • The BP record includes the required setup to create records through Excel in the Auto Creation tab of the BP setup.
  • • All required fields contain values for BP records and line items. If the import fails, the imported file contains error information.

    Note:

    Leading and trailing spaces in data elements (DEs) will be ignored for validations. For example, "cost item" and "cost item ", which includes a trailing space, are treated as identical DEs.

After the initial validation ends, the file is processed further. At this stage, all validations pertaining to system-level rules as well as user-defined form-level validations are performed. In the event of a failure, an email notification with the reason for failure is sent to users and groups identified in the BP setup.

Errors and Corrections

If any row contains an error, Unifier cancels the import and does not create any BP records or line items. When the import file contains errors, Unifier displays this message: Import could not be completed. There are errors in this template file. These errors can be seen in the Import Data Template worksheet. Do you want to download the file and fix the errors? Click 'Yes' to download the file and 'No' to upload a different template file.

Additional Information About Importing a Microsoft Excel Template File

The name of a downloaded file matches the name of the imported Excel template file. If you download a file that contains errors, the Import Data Template worksheet displays errors in the following format:

  • The first row of the worksheet includes this static text in an additional column: Following errors were encountered while importing the data from the template. This column must be deleted after fixing all the errors. If this column exists in the file at the time of import, the import will fail.
  • Errors that are not specific to a row display in the first H row that contains data, for example, when the auto-creation setup is not met.
  • If a row contains multiple errors, all errors appear in the error cell.
  • Each row that contains an error displays the actual error message in the first column.
    • The following error format applies to required fields:

      "Values are missing in required fields: <Field Label 1>, <Field Label 2>"

      For example:

      Values are missing in required fields: Contract Reference.

      "Values are missing in required fields: <Field Label 1>, <Field Label 2>, <Field Label 3>"

      For example:

      Values are missing in required fields: Type, Short Description, Asset Type.

    • For other form-level validations, the message appears in the row.

      For example: Equipment Units Available cannot be less than 10. This number is required for contingency purposes.

Adding Attachments

As with CSV file imports, you can add attachments during the import process. In the Add Attachments step, use the Upload Microsoft Excel File window to add attachments to the BP record or line item. The Review section shows uploaded files and files that are waiting to be uploaded.

For example, after you upload the Microsoft Excel file, all attachments listed in the file appear in the Files to be Uploaded section. As you upload each file, its status changes from Waiting for File to Ready to be uploaded.

In the Review section, sort the Status column as needed.

When you select Upload, Unifier uploads the attachments to the BP record or line item, as required.

Validating Added Attachments

When you upload attachments, Unifier performs the following validations:

  • If the Attachments column in the Excel file does not contain data, Unifier processes the file without attachments.
  • If you specify a name for the Attachment column at the BP record- or line item-level, Unifier ignores the column headings.
  • For Document Type BPs, each line item can include only one attached file.

To validate a BP record-level Excel import:

When you create BP records by importing an Excel file from the Logs node, Unifier does not retain the Attachment option that you selected in the validation form of the integration interface.

If you import attachments with Excel, Unifier allows the import. When the Excel file references files in the Attachments column and Unifier cannot find those files in the attachment payload, the Attachments table displays this message: Some files are still waiting to be uploaded. Do you want to proceed without uploading those files? Select OK to create BP records or line items without attachments from the Excel file and ignore missing attachments. Select Cancel to return to the Upload Attachments step.

If the Status field in the Review section shows Waiting for the file and you select Upload, the Attachments table displays the same message.

To validate a line item-level Excel import:

When you start a line item import from a Line Item tab, and the design does not select Upload documents from My Computer, you cannot add attachments. The Attachments table displays this message: Uploading of Attachments is not allowed.

If the Excel file references files in the Attachments column and Unifier cannot find those files in the attachment payload, the Attachments table displays this message: Some files are still waiting to be uploaded. Do you want to proceed without uploading those files? Select OK to create the line items without attachments and ignore the missing files. Select Cancel to return to the Upload Attachments step.

Note:

If the attachment payload includes files that do not appear in the Files to be Uploaded section of the Excel file, Unifier does not upload those files. In this case, the Status field in the Review section does not change.

Audit Log Information for Imported BP Records

The following information is captured in the Import Audit Log for each workflow and non-workflow BP record created through the Excel import process.

  • Non-Workflow BPs
    • Event: Creation by way of Microsoft Excel Import
    • Action: Create [BP Name]
  • Workflow BPs
    • Event: Creation by way of Microsoft Excel Import
    • Action: [Action Name]