Who is this article for?Users who are accessing Pentana Risk within Classic.
Site Admin permissions are required.
In Pentana Risk you are able to upload bulk information into your system using the 'Imports' feature. This is done by entering your data into a prepared spreadsheet and using one of the Import tools to process the data.
This article explains what type of data can be imported, general guidance on using import sheets and common issues. Individual import types will be covered in dedicated guides.
- What type of data can I import?
- What are Import Spreadsheets?
- Using the Import Spreadsheet
- Entering Data into an Import Sheet
- Deleting Rows in the Import Sheet
- Inserting Rows in the Import Sheet
- Importing your Data
- Downloads
What Type of Data Can Be Imported?
Imports are available for the following data types:
Note: Each individual import type will be covered in detail in their own specific guide.
- Action Notes
- Action Milestones
Feedback
- Feedback Notes
- Local PI's
- PI Data Collection Settings
- PI Values
- PI Annual Target
- PI Notes
- Benchmark
Audit Recommendations
- Internal Controls
What are Import Sheets?
Import Sheets are formatted spreadsheets which include macros to help the imports tool recognise your data. You can download our import sheets from the 'Downloads' section at the bottom of this article or from the Classic Help page.
We recommend downloading a fresh copy of this file each time you do an import, as we can make changes to the import functionality as part of our release schedule.
Important: Please do not make changes to the macros and formulas. Also make sure that you resolve any issues that the spreadsheet flags before import, otherwise your import can fail or result in incorrect data in your Pentana Risk system.
Using the Import Spreadsheet
When you download and open up the Excel document, it's important to 'Enable Editing' and 'Enable Macros' when prompted by Microsoft Excel. If you do not have the correct security permissions to do this then please contact your IT team.
An Instructions sheet is also included which explains what the different columns colour coding means.
Hovering your mouse over each column heading will also give you a tooltip with any guidance.
Tip: We recommend saving the workbook with a meaningful name, such as the date you prepared the data. This prevents you from losing track of the import sheet used if you need to make any modifications.
Entering Data into an Import Sheet
The spreadsheet is locked but allows alteration to the headings so you can change them if you’ve altered your field names on your site. The layout of the information on the sheet is protected to allow for a smooth process when importing the data.
Note: If you are copying data from another spreadsheet or document, it’s advisable to use paste special and paste only the value, this will prevent any unwanted formatting being applied to the data.
There are several data types that can be entered into the Import sheets:
- Short text
- Free text
- Number values
- Dropdown values (these are standard values within the application or validated from the data you have already added)
The columns will indicate what data type is needed, and if the values you have entered match this; however, the spreadsheet cannot validate things like custom field names and values until it has been run through the imports system, so take care to copy these exactly from your application.
Other columns are to validate your data and tell you when something will not import successfully, for example, the 'Title Length' column will show you when the title is over 255 characters.
The Date Checker column will highlight one of three problems with the Due Date, Actual Start or Planned Start date, as follows:
Ownerships
Ownership fields have a specific format that you must follow. These columns must reference Users and Roles that currently exist in the system when the data is being imported.
Defining Users:
Surname, Firstname
Defining Roles:
RoleDescription
Multiple Roles and User can be imported in the same Ownership column. To add multiple ownerships in each column, separate the value with a semicolon:
Surname, Firstname; RoleDescription
Customer Fields and Values
Customer Fields can be imported in some data types using the green columns at the far right of the spreadsheet. To set up an import sheet for customer fields, you must:
- Copy your field names exactly into the grey column header.
- Enter your field values exactly into the data rows where relevant.
When using dropdown values, you can refer to the dropdown option by entering either the option or the code for the option exactly. We recommend that you copy and paste any text values directly from the application to ensure that these values are correct.
Note: If no data needs to be added for a particular green row then you may just leave that cell empty.
Deleting Rows in the Import Sheet
If you make a mistake when entering the data and want to remove a row you will not be allowed to do this as the spreadsheet is locked. This is to maintain the integrity of the data which is calculated using formulas, therefore to work round this you can:
Clear the contents of the cells (by pressing delete) rather than highlighting the whole row and deleting it. The import will ignore the empty row.
Inserting Rows in the Import Sheet
With specific import types such as Actions and Risks, the position of rows in your source data can be important when representing a tree structure.
When building a tree structure you may find you have missed out a parent level and want to add this in but you’re prevented from inserting a row. To add the missed level, just continue adding from the end of the sheet, as the import doesn’t need to have the tree structure in the right order to import.
Once the new parent has been added you can go back and amend the Parent codes as applicable, which will now recognise the new parent row.
Importing your Data
Preparing for Import
When you have finished adding your data to the spreadsheet, make some final checks:
- Column A – All cells showing the wording ‘Clear Cell Before Load’ should be cleared; this is a formula which is not required by the import so it can be removed. The unnecessary formulas need to be highlighted and deleted; you will not be able to delete the entire row.
To do this quickly select the first cell where ‘Clear Cell Before Load’ is showing and hold the shift then control key then press the down arrow on your keyboard, now hit the delete key to remove the contents of the selected cells. - Columns showing a yellow highlight are mandatory. Data must be entered into these rows before
continuing with the import. - The column called ‘Date Checker’ contains a formula which will highlight in red any problems you may have with dates that have been entered. Resolve all errors before continuing.
Data Validation
Open up Classic, and open the import screen for the data type that you are going to import (for more information on how to access the import screen see the individual guides listed at the top of this article).
Click on the 'Open Spreadsheet' button to open a file explorer and select your saved spreadsheet.
When you first open the import sheet in Classic the system will validate the spreadsheet data against your application's data. You will be shown a modal with a list of identified issues:
Clicking OK will dismiss the modal and take you to the import screen.
The validation will be shown as:
- A count of errors at the top of the toolbar
- Red highlighted cells where there is invalid data
- A red marker next to the scrollbar showing where a row has an error.
Hovering over the red cells will display the error message:
To amend the data, open the spreadsheet in Excel again, modify the incorrect data and save, and reopen the Imports screen to re-validate. Once there are no errors the 'Import' button in the toolbar will be active.
Running the Import
Once the 'Import' button is active, click on it to begin importing your data.
Note: If you are importing large amounts of data at once then this may take some time.
Once the import has completed successfully you will be shown a 'Completion' message.
If the Import fails, all data that has successfully been imported will be removed, and you will be shown an error message explaining why the process has aborted.
Downloads
Comments
0 comments
Please sign in to leave a comment.