Upload of users from an Excel file

The user upload function allows bulk creation and updating of users from a file in Excel format:

A sample file in the expected format is available from the screen for the upload.

The file must conform to the format of the sample file available in the upload dialog with the following exceptions:

1/ the columns can be reordered

2/ new columns (e.g., for recording notes) can be added to the file. These columns will be ignored by the upload process

3/ new rows can be added at the top of the file, above the header line. The rows will be ignored by the upload process

4/ additional worksheets can be added to the Excel workbook. Worksheets that conform to the format of the 'MDS' worksheet in the sample file will be processed by the upload.

The upload processes each row in each worksheet with a header line as specified in the sample file. Each row is treated as a user. New users are distinguished from preexisting users based on their email address. If an email address in the file matches a user in the system, the upload will use the user record from the system. By default, the upload only creates new users (i.e., records with an email address not assigned to any user). If option 'Allow updating of existing users from the information in the file uploaded' is set, the upload will both create new users and apply the data in the file to preexisting users.

Since the upload uses the email field to identify existing users, the upload cannot be used to update the email address for users. Any attempt to do so will result in the creation of new user records rather than the updating of existing user records.

The upload processes the file as soon as it is loaded. However, no data is saved in the system until button 'Save' is pressed at the bottom of the dialog. If no data can be saved (e.g., the file is empty), then the 'Save' button is not available.

For each worksheet conforming to the expected format in an uploaded workbook, the number of records that can be loaded is indicated on the screen. This number depends on whether the option to update existing records is selected as well as data errors for records in the worksheets. The number of records that can be loaded ranges from 0 to the number of records in the file. For a file loaded previously and when the option to update users is not selected, the number is 0.

Handling of errors

Errors are handled at multiple levels by the upload:

1/ if the file provided is not a valid excel file or if the file does not contain any worksheet that conforms to the expected format, a message to that effect is displayed

2/ if errors are detected for some of the rows (e.g., a record omits an email address) then a modified version of the uploaded file can be downloaded from a link on the screen. In the download, errors are identified by a colored background for the cells with the errors as well as a message in the 'Error' column in the first position in the file. The colored background is either red or blue. A red background indicates a fatal error that prevents the user record to be loaded. A blue background identifies a warning. Records with warning can be loaded. However, the data in the cells with warnings will be disregarded.

3/ if more than three consecutive rows are blank in the file, no row is processed that is below the blank rows

4/ both full and short names for sites and shifts must by distinct to use the user upload module

The upload allows the creation of both users (e.g., support staff) and providers. A record in the file is considered a provider if the columns for the print name and the provider activity status are populated. If neither column is populated, the record is treated as a user record.

Field details

The fields supported for records in the upload file are as follows. For cells that can contain multiple values (e.g., 'Canâ€™t Do Shifts'), the values must be separated by a line break. To create a line break within a cell, use the 'Alt+Enter' key combination.

	Field	Details
User	First Name	The first name for the user Free text Case sensitive Mandatory
	Last Name	The last name for the user Free text Case sensitive Mandatory
	Email	The email for the user Free text Mandatory Identifies the user Converted to lower case by upload
	Active	Y or T or N or F Mandatory
	Title	Free text Case sensitive Optional
	Department	Free text Case sensitive Optional
	Gender	M or Male or F or Female Optional
	Time Format	12 or 24 Optional
	Preferred Location	Code for a location in the current group Location does not need to be the same as the current location
	Phone Land 1	Text in format: nnn-nnn-nnnn
	Land 1 Extension	Text in for nnn or xnnn
	Land 1 Private	Y or T or N or F Mandatory when Phone Land 1 is defined
	Phone Land 2	Same as Phone Land 1
	Land 2 Extension	Same as Land 1 Extension
	Land 2 Private	Same as Land 1 Private
	Phone Mobile 1	Same as Phone Land 1
	Mobile 1 Provider	Name of the mobile service provider such a Verizon or Sprint Case insensitive
	Mobile 1 Private	Y or T or N or F Mandatory when Phone Mobile 1 is defined
	Phone Mobile 2	Same as Phone Land 1
	Mobile 2 Provider	Same as Mobile 1 Provider
	Mobile 2 Private	Y or T or N or F Mandatory when Phone Mobile 2 is defined
	Fax	Same as Phone Land 1
	Fax Private	Y or T or N or F Mandatory when Fax is defined
	Pager	Same as Phone Land 1
	Pager Private	Y or T or N or F Mandatory when Pager is defined
Provider	Provider Active	Y or T or N or F Mandatory when Print Name is defined
	Print Name	The print name for the provider Must be unique within the location
	Provider Group	The name of the provider group for the provider Must match the name of a provider group for the location Case insensitive
	External Id	Free text Optional
	Certify Changes	Determines if the provider has this privilege Y or T or N or F Optional
	Requires Approval	Determines if the provider has this privilege Y or T or N or F Optional
	Modify Schedules	Determines if the provider has this privilege Y or T or N or F Optional When set, Requires Approval is implied as F
	Approve Changes	Determines if the provider has this privilege Y or T or N or F Optional When set, Modify Schedules is implies as T
	Total Workload	Determines the total workload for the provider Number or equal or = Mandatory for a provider
	High Priority	Determines the high priority workload for the provider (relative to the high priority category) Number of equal or = or % or percent Optional
	Medium Priority	Determines the medium priority workload for the provider (relative to the medium priority category) Number of equal or = or % or percent Optional
	Low Priority	Determines the low priority workload for the provider (relative to the low priority category) Number of equal or = or % or percent Optional
	Shifts in Row	Determines the number of shifts in a row for the provider Number with or without suffix Suffix ! indicates that the number should be treated as an exact value
	Category in Row	Determines the number of days in a row where the provider can be scheduled for a shift included by the Category in Row Number with or without suffix Suffix ! indicates that the number should be treated as an exact value
	Link Sat-Sun	Determines if Saturday and Sunday are linked or the provider Y or T or N or F Optional
	Can't Do Shifts	The shifts for which the provider is Can't Do Multi-valued Each value must match the full name or short name for a shift
	Can't Do Sites	The site for which the provider is Can't Do Multi-valued Each value must match the name or abbreviation for a site for the current location
	Can't Do Weekends	Determines if weekends are Can't Do for the provider Y or T or N or F Mutually exclusive with High Disliked Weekends, High Preferred Weekends, Disliked Weekends, and Preferred Weekends
	High Disliked Shifts	The shifts for which the provider is High Disliked Multi-valued Each value must match the full name or short name for a shift
	High Disliked Sites	The site for which the provider is High Disliked Multi-valued Each value must match the name or abbreviation for a site for the current location
	High Disliked Weekends	Determines if weekends are High Disliked for the provider Y or T or N or F Mutually exclusive with Can't Do Weekends, High Preferred Weekends, Disliked Weekends, and Preferred Weekends
	High Preferred Shifts	The shifts for which the provider is Can't Preferred Multi-valued Each value must match the full name or short name for a shift
	High Preferred Sites	The site for which the provider is High Preferred Multi-valued Each value must match the name or abbreviation for a site for the current location
	High Preferred Weekends	Determines if weekends are High Preferred for the provider Y or T or N or F Mutually exclusive with Can't Do Weekends, High Disliked Weekends, Disliked Weekends, and Preferred Weekends
	Disliked Shifts	The shifts for which the provider is Disliked Multi-valued Each value must match the full name or short name for a shift
	Disliked Sites	The site for which the provider is Disliked Multi-valued Each value must match the name or abbreviation for a site for the current location
	Disliked Weekends	Determines if weekends are Disliked for the provider Y or T or N or F Mutually exclusive with Canâ€™t Do Weekends, High Disliked Weekends, High Preferred Weekends, and Preferred Weekends
	Preferred Shifts	The shifts for which the provider is Preferred Multi-valued Each value must match the full name or short name for a shift
	Preferred Sites	The site for which the provider is Preferred Multi-valued Each value must match the name or abbreviation for a site for the current location
	Preferred Weekends	Determines if weekends are Preferred for the provider Y or T or N or F Mutually exclusive with Can't Do Weekends, High Disliked Weekends, High Preferred Weekends, and Disliked Weekends
	Day Cycles	The day cycles that apply to the provider Multi-valued Each value must match the name of a monthly cycle for the location The number of day cycles and day cycles start date must be the same
	Day Cycles Start	The start dates for the day cycles that apply to the provider Multi-valued Each value must represent a valid date The number of day cycles and day cycles start date must be the same
	Monthly Cycles	The monthly cycles that apply to the provider Multi-valued Each value must match the name of a monthly cycle for the location