Importing and Exporting Users- Including the Bulk User Upload Spreadsheet

Document created by 1000256 Administrator on Mar 24, 2014Last modified by 2261986 on Dec 15, 2017
Version 29Show Document
  • View in full screen mode

The RLMS provides an option to handle a mass import or mass export of multiple/all users via an Excel Spreadsheet.  The following information will explain how to handle the importing and exporting processes.   Please see the very bottom of this document for the exportable Excel document. 

Importing Users

While it is possible to add or edit users manually (Creating and Editing Users Manually), it may not be the most convenient option if you need to add or edit a large number of Learners at one time.  In this scenario, it is better to use the Import Users function.

  1. As an Administrator, click the Users tab
  2. Click the Import Users link. You will need to have the UserData Spreadsheet (you may also hear it called the Bulk Load file or the Import Users Spreadsheet) completed prior to importing.  Please see below to download the file and to learn more about entering the user data into the spreadsheet.


  3. After preparing and saving the UserData Spreadsheet, please click Choose File/Browse (this depends on your browser).  Locate the .txt or .xml file on your computer and click open.
  4. It is recommended to select the Only validate the file option  to verify that the import will be successful.  This will not upload the file, but will scan it to ensure that there are no major issues or exceptions that need to be corrected.
  5. Click Upload.
  6. If you are validating the file, the system will advise you if the file is likely to be successful or if any errors are found.

a.If the file has any errors, you will see a message similar to this one. You can then select the "Download Results File" button to download a file that will include your data with additional columns "Result" (Success/Failed) and "Result Message" that gives the reason the result failed.

 

b. If the file has no major exceptions or errors, you will see this message.  Please Note: this does not necessarily mean that your file is perfect or is not missing information, it merely means that there are no major errors that would prevent the file from importing.
validation.jpg

7. Once you have validated the file, select Choose File/Browse again to select the same .txt or .xml file. Uncheck the Only validate the file option.

8. Click Upload.  Once the file is uploaded, the system will let you know if the import was successful.

import success.jpg                                             

Exporting Users

To obtain a hard copy of your user list you will want to use the Export User function.

  1. As an Administrator, click the Users tab.
  2. You can filter the user list by a number of different options:
    1. Alphabetically by last name
    2. Active, Inactive, or All
    3. Any other available filter option you would like to use
  3. After selecting any applicable filters, click the Export Users link.  Clicking this link prompts an Excel spreadsheet download.  After opening the download in Excel you can print or edit the spreadsheet.

Exporting the user list from your site is helpful if you need to edit multiple Learner accounts at one time.
Please Note: the export file looks very similar to the UserData spreadsheet; however, the export file cannot be used to import users due to the internal formatting.  The layout of the columns is the same on the export user list as the UserData spreadsheet, so you are able to copy and paste the data from the export into the UserData spreadsheet.

The UserData Spreadsheet (AKA Import User Spreadsheet, Bulk Load)

You will find the Import User Spreadsheet attached at the bottom of this article.
Please Note: The spreadsheet contains macros.  Your computer may warn you that macros can contain computer viruses.  While this is true, and you should always maintain proper virus protection on your computer, the macros in the Bulk Load spreadsheet are supposed to be there and will not cause any problems for your computer.  Confirm that the sheet opens with the macros prompt, or that it displays below the top menu that macros are enabled (in excel 2007 and later, make sure you enable macros).

Import User Formatting Requirements

Each column must be formatted correctly according to the instructions on the spreadsheet (check the tabs of the spreadsheets for important information about format and data requirements: Instructions, User Profile Definitions, Examples, Data and Roles).There are only 4 required fields that must be filled in on the spreadsheet before it can be uploaded: Last Name, First Name, Username, and Password.  Email address is also a required field for anyone with the Administrator, Supervisor, Instructor, DataEntry, or Observer permission.  While only 4-5 fields are required, we strongly recommend that as many fields as possible be filled in so you can take full advantage of the features that the RLMS has to offer.To learn more about which fields we recommend, please view the Creating and Editing Users Manually article.  That article will explain each field in more detail.

Quality Assurance Checklist and Techniques:

Before you import the file, please check for the following items/common errors:

  1. Typos – Check all columns for typos, but especially in the Report Filter and Hire Date columns (e.g., misspelled report filter values, incorrect dates).  For example, if job title Admin Assistant is accidentally misspelled for one user (Admin Asssitant), then both values will be uploaded creating a duplicate job title in the system.
  2. Blanks Confirm that no blanks exist in the four required columns (first name, last name, username, and password) and that any blanks in other columns are intentional.
  3. Duplicate Usernames or Learner IDs – Check to make sure there are no duplicate user names.  If you have more than one user with identical usernames (e.g., Jennifer Smith and John Smith both have user name of jsmith), only one will be uploaded.  If you have populated the Learner ID column, make sure there are no duplicates.  Learner IDs must be unique if you want to upload historical training data.  See the end of this page for methods to check duplicates.
  4. Format – Make sure your employee data is on the Data tab and not the Examples tab or any other tab.  The first row of users should appear on line 4; any information on lines 1-3 will not be uploaded.  If you have hidden any rows or columns, make sure you unhide them before submitting/uploading the spreadsheet.  You must use the Excel spreadsheet provided for the import to be successful.
  5. Email addresses -  If you have included emails, make sure each user has a unique email address.  If a user does not have an email address, leave it blank and do not use another user's email. Confirm that all users identified as Supervisors, Administrators, DataEntry, Observers, or Instructors have a valid, complete email address.  Any user role other than Learner must have an email address.  All emails must be complete and valid; any typos or omissions will prevent a successful import.  Common mistakes include: leaving off the .com or .org, misspelling, nothing after the @ symbol, comma instead of period, etc.
    • Check for Invalid Email Addresses - A formula you can use to verify the email address is:  =IF(LEN(L4)>0,ISERR(SEARCH("@",L4)+SEARCH(".",RIGHT(L4,5))+ISERR(SEARCH(" ",L4))))=FALSE. Copy and paste the formula in a blank column, starting on row 4 (in the formula, L4 refers to the email address cell) and then copy and paste it all the way down the column as far as you have data.  The results will either be TRUE or FALSE and the ones that say FALSE are email addresses that require modification.  The formula checks for the @ symbol, a period in the final five characters and no spaces.  This is not going to check every possible typo, but it will help considerably.
  6. Format: Column Definitions Tab – Review the column definitions tab to view the required format of information in each cell.  If you used formulas in Excel to populate fields like email, username, password, etc., you must remove the formula from the sheet and put the values/actual data in the cells instead.  The upload will not work if formulas are in any field on the sheet.
  7. Count Users – Count how many users you will upload on the sheet.  Then, check how many users are in the site by going to Users and viewing the Active User count.  After completing the upload, go to Users > User Management and make sure the total number of users in the site is correct.  If there are a few missing, recheck the bulk load.  Usually, the most common mistake is duplicate usernames.  Additionally, make sure you are not exceeding your contracted number of users.  If you need to modify your contract to allow for more users, please contact the Support Center.
  8. Users Assigned to Hierarchy Folders – Make sure each user is assigned to a Hierarchy folder.  To obtain the Hierarchy ID, please see Creating and Editing the HierarchyEach hierarchy folder can only contain one warning email recipient, so please confirm that column AA has only one X for each hierarchy folder.
  9. User Category – This feature is typically used for assigning training plans to users in bulk; however, it also can act as a report filter in some reports.  You must separate multiple User Categories for a user with a semi-colon. 
  10. Check for Typos and Blanks – Use auto filter to easily scan through the values contained in a column without having to view every instance of the values.  You can also see the blanks in a column and confirm all blanks are intentional.
  11. Check Duplicate Values – In Excel 2007 and later, highlight the column you want to check for duplicate values.  On the Home tab, go to Conditional Formatting > Highlight Cell Rules > Duplicate values.  Every value in the row that has a duplicate will be highlighted and you can easily find and modify them.
  12. Macros Enabled – Confirm that the sheet opens with the macros prompt or displays below the top menu that macros are enabled (in excel 2007 and later, make sure you enable macros).
  13. Reformat before Creating .xml – The spreadsheet needs to be reformatted to upload successfully, the instructions on how to do this are on the first tab of the spreadsheet attached.

 

To get back to the Knowledge Base and the master list of topics, please click here: RLMS Knowledge Base

Outcomes