[Index]
Overview
A bulk load template is a Microsoft Excel .xlsx format spreadsheet workbook that contains a single sheet, and is used for bulk loading data into VOSS Automate.
A tabbed workbook may contain two or more template sheets (one sheet per model). When using a tabbed workbook, bulk load transactions are carried out from left to right, starting with the far left tab, and ending at the far right tab. For example, when adding a site under a customer in a bulk load, ensure you add the customer sheet to the left of the sheet containing details of the associated site, so that the customer detail is loaded before the site.
You can use any filename for the bulk load workbook, but since the same file can be loaded multiple times, it is recommended that you use unique names to differentiate bulk uploads.
Bulk Load Limitations
VOSS Automate bulk load automation templates employ advanced features, such as configuration templates (CFTs), customizable field display policies (FDPs), and GUI rules.
For some resources, generated bulk load templates won't produce the provisioning results that may be achieved when using the GUI to upload and configure data. This topic provides an overview of the bulk load limitations to consider for such scenarios.
Note
See the Bulk Load Reference Guide for more information around the specific resources where these limitations apply, the impact of the limitations, and for best practice advice for using generated loaders for various resources.
The table describes the general bulk load limitations:
| Limitation | Description |
|---|---|
| Certain fields link together different resources | These fields might be hidden in the GUI, or they may be read-only. In generated bulk load templates, these field are currently exposed as mandatory fields. The fields and the specific conventions that are used in the template to link the fields together are highlighted in notes specific to the resource. For example, the value for remote destination name should be specified as RDP-<username>. |
| Certain fields are derived from other system data | Notes specific to the resource highlights where to obtain possible values for such fields. Examples are key-value type fields of a phone's vendor configuration settings. |
| GUI rules defined in the user interface that aren't replicated in the backend workflow must be considered in the loader to achieve the same provisioning results as the GUI. | Examples - GUI rules may:
|
The image shows that A GUI Rule may, for example, disable input fields based on the state of a checkbox. On the worksheet, the selected checkbox is represented as TRUE in the column. Columns associated with the disabled fields should not be filled.
To overcome the complexities introduced by the bulk load limitations, a set of sample bulk load sheets have been generated that enable users to get started quickly and to leverage the best practices developed by VOSS.
The latest sample bulk loaders can be obtained from your account team.
Bulk Loading Files
This procedure uploads two or more .xlsx worksheets in a bulk upload.
Prerequisites
Note
Spreadsheet formulas in data are ignored, for example: '=7+2'
To bulk load files
In the Admin Portal, go to (default menus) Administration > Bulk Upload to open the Build Load page.
Set the hierarchy to the level where you want to add bulk data.
On the Bulk Load page, click Choose Multiple; then, browse to the files you wish to upload.
Choose an option:
Click Bulk Load.
Wait for the transaction to complete, then view results.
Note
To access the latest documentation, go to Documentation and Resources at: https://voss.portalshape.com
Completed Bulk Load XLS sheets can be loaded immediately. Verify the following:
The bulk loader tools enable the quick and easy management of system data using pre-populated MS Excel formatted spreadsheets.
A spreadsheet template can be generated by the system for any of the resources in the system - either from the GUI or the API. For Configuration Templates, the sheet cannot be used to bluk load - they should be imported as a JSON file.
The data on the sheet includes column headers to indicate the hierarchy, action, search criteria and attribute names of the model to which the data applies. Rows include the data for model individual instances.
Use a single sheet in the file to manage multiple templates by adding additional header rows and data under them. A file can include multiple sheets with a single or multiple templates on each.
When the file is loaded, it can either be processed immediately or scheduled for a date and time. A scheduled bulk load file is listed on the Schedule list view as a Single Execution schedule type and with resource type of data/BulkLoad. Items on the Schedule list are deleted once the scheduled item has been executed. This means that after a scheduled bulk load has been executed, you will no longer see it in the list of schedules.
A single parent transaction is created for the entire bulk load. Unless a sheet is set to execute rows in parallel, each row in the bulk load sheet results in a separate sub-transaction that is executed sequentially and synchronously. If a single sub-transaction fails, the bulk load transaction continues and does not roll back the preceding sub-transactions. In the case where a bulk load sub-transaction has other sub-transactions - for example a provisioning workflow with multiple steps - failure in any of the steps will cause a roll back of all the steps in the bulk load sub-transaction.
If a sheet is set to process rows in parallel, then by default, 14 rows are processed in parallel. Refer to the topic on the bulk load sheet layout for more details.
If a file is processed and further files are loaded, they are processed in parallel. Thus, bulk load transactions are executed in parallel, as with all transactions. Bulk load transactions are executed immediately.
Transactions, once started, cannot be canceled.
The transaction log is available on the user interface after a bulk load transaction has been run. Refer to the topics on transactions and viewing transactions in the documentation.
Go to the Transaction menu. Bulk load transactions show in the log:
In the list view, the bulk load is shown in the Action column of the log. If the bulk load was scheduled, this is shown as a schedule with the detail column indicating it to be a bulk load. The Action column will show "Execute Bulk Load" or "Execute Schedule" respectively.
The submitted, start and stop time for the entire bulk load transaction is also shown.
The Detail section will hold the name of the file that is bulk loaded as well as the workbook sheet number and the number of successful rows out of the total, for example:
[ 8/9 ] succeeded from [ 1 ] sheet in data_Users_bulkloadsheet.xlsx.
Checks are made to validate the user's access profile, the provided hierarchy information and data constraints for the bulk load transaction when updating the target models. The parent bulk load transaction will show the error message if this validation fails and no rows will be loaded.
Where rows are loaded, each row in the bulk load sheet appears as a sub-transaction within the bulk load transaction. The Message box shows the number of successful and failed rows loaded.
For each loaded sheet, bulk load transactions are run in series for each row. Multiple bulk load sheets can be loaded and these transactions will load in parallel.
Sheet rows can be processed in parallel. The sheet should then not contain multiple, sequence dependent models. Refer to Bulk Load Sheet Layout.
For each row of the bulk load sheet carrying out the default add action, a Create action is shown on the list of transactions. Sheet rows that led to a successful Create action have a Success status, while rows that failed show a Fail status. If a row fails, the load process continues. For failed actions, the transaction can be selected to show the error message.
If one or more rows of the sheet failed to load, the Bulk Load Sub Transaction shows a Success status, while the Log list will show "error" for failed rows.
On the list of sub transactions, you can inspect the details of each sub transaction. For example, the submitted, start, and stop time for the bulk load sub transaction corresponding with a row on the bulk load sheet is shown. In the case of a failed sub transaction, further information about the failure - such as the error message and row data - is shown in the sub transaction.
A canceled bulk load transaction means the Processing worksheet sub transaction, as well as all sub transactions within the worksheet transaction in a Processing or Queued state, will fail.
For parallel transactions, multiple resource transactions may be in a Queued or Processing state. By default, 14 rows are processed in parallel. Refer to Bulk Load Sheet Layout for more details. If a worksheet transaction fails as a result of bulk load transaction cancellation, subsequent worksheet tabs in the bulk load workbook will not be processed by the bulk loader.
Completed Bulk Load XLS sheets can be loaded immediately. Verify the following:
The transaction log is available on the user interface after a bulk load transaction has been run. Refer to the topics on transactions and viewing transactions in the Core Feature Guide.
Choose General Tools > Transaction. Bulk load transactions show in the log:
In the list view, the bulk load is shown in the Action column of the log. If the bulk load was scheduled, this is shown as a schedule with the detail column indicating it to be a bulk load. The Action column will show "Execute Bulk Load" or "Execute Schedule" respectively.
The submitted, start and stop time for the entire bulk load transaction is also shown.
The Detail section will hold the name of the file that is bulk loaded as well as the workbook sheet number and the number of successful rows out of the total, for example:
[ 8/9 ] succeeded from [ 1 ] sheet in data_Users_bulkloadsheet.xlsx.
Checks are made to validate the user's access profile, the provided hierarchy information and data constraints for the bulk load transaction when updating the target models. The parent bulk load transaction will show the error message if this validation fails and no rows will be loaded.
Where rows are loaded, each row in the bulk load sheet appears as a sub-transaction within the bulk load transaction. The Message box shows the number of successful and failed rows loaded.
For each loaded sheet, bulk load transactions are run in series for each row. Multiple bulk load sheets can be loaded and these transactions will load in parallel.
Sheet rows can be processed in parallel. The sheet should then not contain multiple, sequence dependent models. Refer to the topic on Bulk Load Sheet Layout.
For each row of the bulk load sheet carrying out the default add action, a Create action is shown on the list of transactions. Sheet rows that led to a successful Create action have a Success status, while rows that failed show a Fail status. If a row fails, the load process continues. For failed actions, the transaction can be selected to show the error message.
If one or more rows of the sheet failed to load, the Bulk Load Sub Transaction shows a Success status, while the Log list will show "error" for failed rows.
On the list of sub transactions, choose the transaction Link hyper-link to inspect the details of each sub transaction. For example, the submitted, start, and stop time for the bulk load sub transaction corresponding with a row on the bulk load sheet is shown. In the case of a failed sub transaction, further information about the failure - such as the error message and row data - is shown in the sub transaction Link.
A canceled bulk load transaction means the Processing worksheet sub transaction, as well as all sub transactions within the worksheet transaction in a Processing or Queued state, will fail.
For parallel transactions, multiple resource transactions may be in a Queued or Processing state. By default, 14 rows are processed in parallel. Refer to the Refer to the topic on Bulk Load Sheet Layout for more details. If a worksheet transaction fails as a result of bulk load transaction cancellation, subsequent worksheet rows and tabs in the bulk load workbook will not be processed by the bulk loader.