Excel File Format for Bulk Update Export/Import Process

This section applies to BPs that export BP records to Microsoft Excel (.xlsx) by using the Bulk Update option. See Exporting BP Records Using Bulk Update From Business Process Log Node.

Note:

Bulk Update is not supported for General Spends Classification of Cost BPs with line items of CBS Code, whether workflow or non-workflow, when breakdown rows exist in the referenced Schedule of Values (SOV).

When you select BP records by using the Bulk Update menu options, Unifier saves the exported Excel file in one of the following formats:

  • For BPs that contain only Main Form data: [BP Name]_MainForm.xlsx.
  • For BPs that contain Main Form data and line item data: [BP Name]_MainForm_LineItems.xlsx.

Worksheets in the Exported File

The exported and imported Excel file includes the following worksheets:
  • Instructions: Unifier generates this worksheet automatically. The worksheet content depends on the BP type and describes how to use the Excel file to enter or update BP record and line item data.
  • Main Form: Unifier generates this worksheet automatically as the second worksheet in the Excel file. Do not rename or reorder this worksheet. The content of this worksheet depends on the BP types included in the export or import. It contains the Unifier data required to update existing BP records and line items.
    • Each BP record is identified by the following read-only fields, in order: Record ID and Record Number
    • The following fields and (DEs) are also read-only: Record Status, Creator, Creation Date, Currency ID, Due Date, Status in WF, BP, Formula Data Elements, and DEs that are set to auto-populate from the sum of all line items DEs from the Integration Validation Form
    • The line item tab order matches the order of the selected records.
    • Custom date formats are preserved.
    • DEs with direction set to Input or Both appear as fields in the exported Excel file.
    • The worksheet name is translated according to the user’s language preference.
  • Additional worksheets for each line item tab: Each Line Items worksheet displays line item records in the following format:
    • The line item records and the DEs for each tab appear in the same order as they appear in the user interface.
    • Each line item record is identified by the following read-only fields, in order: Record ID, Line Item ID, Record number, and Line No. These fields cannot be renamed, reordered, duplicated, or removed from the file.
    • Formula DEs and auto-populated DEs are read-only.
    • Worksheet names do not support the following special characters: \ / ? * : [ or ]
  • Codes and Descriptions: The fields in the Codes and Descriptions worksheet depend on the Cost Sheet. At a minimum, the worksheet includes the following fields:
    • Cost Code: Displays the cost codes available in the project cost sheet, including active cost codes and inactive cost codes with the full hierarchy.
    • Cost Item: Displays the name of the item associated with the cost code.
    • Status: Displays the status of the cost code at the time of export. Valid values are Active and Inactive.
  • SOV Line Items: If Unifier generates an SOV Line Items worksheet, it includes the following fields:
    • refiid (label of field): A list of contracts associated with the records in the Line Items Worksheet.
    • Ref (label of uuu_sovlinum field): A list of all reference codes from the SOV sheet of the selected contract.
    • CBS Code: A range of CBS codes associated with the contract.
    • Commit Short Description: A short description of each commit.