User Management - Part 7 - User Importer

Importing Users in Bulk with an Excel™ Sheet

Introduction

There are times when multiple users need to be loaded into iPassport and the New User feature only allows adding one user at a time. To make this process more efficient, the Import Users feature allows loading a number of users in one operation.

The importer tool can be found in the ‘Users’ area by navigating to ‘Administration > Users > Import Users’:

Records uploaded to the staging area of importer tools will not be added to the account until the final import operation is completed. They will remain in a ‘reception’ area until all parameters are validated and one of the import options is clicked.

Permission Requirements

The Import Users tab is found by navigating to Administration > Users. It’s only visible to users with the permission, “User Accounts:Create User Accounts” (included in the system role, “Administration Editor”). The user performing the operation will need this permission in every OU which is declared as the Home OU of any of the users being imported.

Features and Considerations

A new framework has been developed for this tool and other importer tools in the system. The aim is to make it easier for users to quickly populate their iPassport accounts with information migrated from other systems.

• This importer tool accepts spreadsheets in Excel™ format (.xlsx, and the legacy .xls) as well as in CSV format. There is no need to convert ‘.xlsx’ files to CSV here. Any other file type is refused with the message, “Upload failed: file type ‘…’ is not supported.”, and a file which cannot be opened, because it is password protected or corrupt, with, “Upload failed: the file could not be opened. It may be password protected or corrupted.”
• The importer progressively validates the information, so large numbers of records can be processed.
• The staging area provides a much clearer and easier environment to edit the metadata and fix problems where required.
• When a record is marked as duplicate (within the staging area), the status will not be automatically cleared if the original record is deleted or uploaded. If the duplicate record is edited, after the original one is removed, the system will revalidate it and clear the ‘duplicate’ flag.

The data can be uploaded to a staging area in three steps and can then be checked and adjusted before importing it to the records within the account.

Step 1 - Download a Template

To facilitate the import, a formatted Excel™ template is available to download and populate, so the information can be uploaded to the system in a structured manner. Simply click the link, ‘Download blank Excel file’, to retrieve a template which you can open in Excel.

Step 2 - Preparing the Data

The template file will have a header row with the eleven fields which will be imported. This row should not be altered; no additional columns should be added and none should be removed. If a column is added or is missing, the system will reject the file with the message, “Upload failed: column headers do not match.”, followed by the headers it expected and the ones it found; a file with a header row but no data rows beneath it is rejected with, “Upload failed: the file does not contain any data rows.” The columns are matched by name rather than by position, so a re-ordered file is still accepted, but keeping them in the order they are presented remains the safest approach. The red triangles in the top right corner of cells in the header row, indicate that a ‘help’ comment is available when you hover over the cell. Not every column carries one: First Name, Last Name and Email have no help comment, the other eight columns do.

To populate the spreadsheet, use one row for each material record and please follow these guidelines:

There are 2 types of fields on the spreadsheet - free-text fields and look-up fields.

Free-Text Fields

This free-text field is compared with other information in the account to ensure uniqueness:

• The Username has to be unique across the account; it’s not required to enter a username; leaving it blank will prompt the system to generate a unique username

These fields require a string of text:

• First Name - required field
• Last Name - required field
• Email - this field is not required but strongly recommended; the system uses emails to notify users of any updates, tasks or requests. The address has to be a valid email address, and in accounts which use SAML single sign-on with the email address as the user identifier it also has to be unique across the account, otherwise the row is flagged, “Email ‘…’ is already in use”
• UID - this unique ID is only required in accounts which use single-sign-on (SSO) strategies. Where it is required, a blank value is flagged, “UID is required”, and a value already held by another user, or by another row in the queue, is flagged, “UID `…` is already in use”

Look-up Fields

The following fields reference records elsewhere in iPassport. It is important that the spelling of these exactly matches the record name or iPassport will not be able to pair them up. The matching is not case-sensitive, but because the spelling itself still has to be exact, the most reliable way to get the information across is to copy/paste from the records in the system to the spreadsheet. For User Group Membership and Home OU, either the long name or the short name can be used; Skilled Group Membership and Distribution List Membership are matched on the full record name only.

• User Group Membership - these can be multiple, separated by a semicolon; user groups define the permissions and areas the user can access
• Skilled Group Membership - these can be multiple, separated by a semicolon; ‘skilled groups’ generate the reading tasks
• Distribution List Membership - these can be multiple, separated by a semicolon; distribution lists are like mailing lists, where multiple users can be grouped under one item
• Home OU - the Organisational Unit where the user’s records will be kept. On enterprise accounts this field is required: a blank value is flagged, “Home OU is required”, and the record cannot be imported until it is filled in

These fields accept only a fixed set of values. Note that the template spreadsheet itself has no dropdown menus, so the values have to be typed in; dropdowns are offered for these fields in the ‘Edit User’ pop-up window in the staging area, covered further down this article:

• Time Zone - this is only necessary if staff of the organisation live in different time zones; if left blank, the system will use the time zone declared in the Settings area. In the spreadsheet the value has to match a valid time zone name exactly, including its capitalisation, or the row is flagged, “Time Zone `…` is not a valid time zone”
• Send Welcome Email? - accepts ’true’ or ‘false’ values; blank values will be treated as true, and an email will be sent

Step 3 - Importing the Data

Once the spreadsheet is ready, it can be simply saved and uploaded in one of two ways:

In the start page of the ‘Import Users’ tab, before any records have been added:

• Drag it into the dotted line rectangle labelled, ’Click or drag and drop the Excel file to add new Users to the import queue’
• or, click inside the rectangle and use the browser window which pops up to find and select it.

After adding user records, additional spreadsheets can be uploaded in the staging page:

• Click the button, ‘Add New Records’
• Drag the file into the dotted line rectangle labelled, ’Click or drag and drop the Excel file to add new Users to the import queue’
• or, click inside the rectangle and use the browser window which pops up to find and select it.

The data imported will immediately start appearing on the screen and the system will quickly validate all the information. Feedback is available on this screen to easily identify and fix problems.

This image shows all the fields:

But, they normally don’t fit on the screen and scrolling is required to reach the fields on the right:

Reviewing Data and Preparing to Import

The staging area will display all records which have been uploaded.

It has a Search field to help locate particular records, and two filters:

• Show Errors Only - which keeps only the rows that have at least one invalid cell. Note that this filter looks for invalid cells rather than for warning triangles, so it also hides rows which have not been validated yet, and rows which failed only a permission check (a yellow warning triangle with no pink cell)
• Show Only My Imports - which hides all rows added by other users

Four buttons are available at all times:

• Clear Import Queue- This button will delete all the records in the staging area.
  • If rows are selected by ticking their checkbox in the left column, the label will change to, ‘Delete User’ for a single row, or ‘Delete x Users’ from two rows upward, where ‘x’ is the number of rows selected.
    
    
• Import All Users- This button will import all rows below which have passed validation.
  • If rows are selected by ticking their checkboxes in the left column, the label will change to, ‘Import User’ for a single row, or ‘Import x Users’ from two rows upward, where ‘x’ is the number of rows selected.
    
    
• Add New Records - Records can be added to the staging area by clicking this button. It will bring up a pop-up window like the initial screen (before the staging area is populated), where a template can be downloaded, if needed, and where a populated file can be uploaded.
  
  
  
• Edit in excel - Clicking this button will invoke a pop-up window, similar to the one above, containing a link to a spreadsheet populated with the information currently in the staging area (’Download Queue in Excel file’). It also offers a place to upload the edited file again.
  • Before clicking the button, rows can be selected to condense the list and only include in the Excel file those records which need amending. It is the selection alone which narrows the export: the two filters do not restrict it on their own, so filtering without also ticking the rows exports the whole queue. Filtering does drop rows from a selection already made, so filter first and tick the rows afterwards.
  • The exported file has an extra leading column, ‘Importer ID’, which is what matches each row back to its record in the staging area. As the pop-up warns, it should not be touched. If it is altered or removed, the re-upload is refused with one of: “Importer ID column missing”, “There are records that are missing an Importer ID”, “Importer ID ‘…’ is not a number” or “Importer ID of ‘…’ not found”.
    
    

The Staging Table

The information is displayed in a table with the following columns and features:

• Checkbox column on the left
  • One or multiple rows can be individually selected.
  • The ‘SHIFT’ (⬆️) key can be used to select or unselect multiple rows.
  • The checkbox in the header area selects/unselects all the rows below.
  • When at least one checkbox is ticked, an additional button appears above the table - ‘Edit User’ when a single row is ticked, or ‘Edit x Users’ from two rows upward, where ‘x’ is the number of rows selected.
• STATUS - If information is missing or not found by the system, a yellow warning triangle will display on the row of the problem record. Hovering over the triangle will bring up a message listing the errors. When a record is ready to import, the status will show a green thumbs-up. A third icon, a grey warning triangle with the message ‘This record has not been validated yet’, is shown while a record is still waiting to be checked. Records are validated one at a time, so on a large file this state is visible for a while.
• Record data uploaded from the spreadsheet:
  • FIRST NAME
    
  • LAST NAME
    
  • USERNAME
    
  • EMAIL
    
  • USER GROUP MEMBERSHIP
    
  • SKILLED GROUP MEMBERSHIP
    
  • DISTRIBUTION LIST MEMBERSHIP
    
  • HOME OU
    
  • TIME ZONE
    
  • UID
    
  • SEND WELCOME EMAIL?
• ACTIONS - There are three icons in this column:
  • Magnifying glass (?) - to open the ‘Edit Users’ pop-up window; additionally, the pop-up window can be opened anywhere in the row where the pointer turns to a ‘pointing hand’
  • Import row (↥) - to import a single row if it’s ready
  • Trash/Bin (?) - to delete a single row
    
    

Fixing Errors

If any of the cells have an error, they will be highlighted in pink.

If a row still displays a yellow warning triangle in the status column, and no cells appear to be invalid (highlighted in pink), it might be a permissions issue. Rows like these are hidden while the ‘Show Errors Only’ filter is on, because that filter looks for invalid cells, so switch it off when you are looking for them.

Clicking a cell (where the pointer turns to a hand) or the row’s magnifying glass, will bring up the ‘Edit User’ pop-up window, which includes all the fields of the single row so they can be edited.

• Fields which have an error will display the reason in small red text below them.
• Clicking any of the ‘Look-up Fields’, will bring up a dropdown menu of all options available. You can scroll down the list or start typing to jump to matching items on the list and click the desired item when located.
  
  

Once fixed, click Save User to validate, update and return to the staging area.

If multiple rows contain the same error, they can be selected and clicking the button, ‘Edit x User(s)’ will allow editing and applying the same fix to all the selected records. In this case, reasons are not provided for cells with errors; the fields will state, ‘Multiple values, click to edit…’. Upon clicking in a field, the message, ‘Leave this blank to clear on all selected records.’ will appear below it, with the option to ‘Cancel edit’.

When a single user is being edited and an error declares that the item was not found, another item can be selected if available, or the item which is producing the error can be selected and then removed before saving, to clear the field.

Importing the Users

There are three ways of importing users once they are ready:

• Import a single row - Clicking the ‘Import’ (↥) icon in a row will import that record alone.
• Import multiple rows - multiple rows can be selected by ticking their checkbox and the button, ‘Import x Users’ (or ‘Import User’ when only one row is ticked) will import them in one step.
• Import all rows - Clicking Import All Users will import all rows displayed below which have passed validation.

A confirmation dialogue box will appear, stating how many users are to be imported.

Once imported, the users will be added in the corresponding Organisational Units within the Users area.

Setting the field, “Send Welcome Email?” to false, will prevent the new users from receiving an email with login credentials.

Whether or not a welcome email is sent, every imported user is created with a password generated by the system and is required to change it the first time they sign in.

• If welcome emails are withheld, they can be sent out later, once the account is ready, by opening the user record (under, ‘Administration > Users > Search Users’) and selecting, ‘Re-send Welcome Email (resets password)’ from the Actions dropdown menu. The password will be reset but only a welcome email with fresh credentials will be sent out. This action only appears in the Actions menu when all three of the following are true: you have permission to reset passwords, the account is not using an SSO login strategy, and the user has never signed in. Once a user has logged in for the first time it is no longer offered.

This is the last article in this user guide.

Previous Step:

The previous article covers User Management Tools