DrugTestNetwork’s online application software for drug and alcohol testing data management and random selection has easy to use import features to manage the upkeep of employer personnel rosters / random selection participant lists.
It’s a big challenge for TPAs to keep employee rosters up-to-date for the random selection of participants for drug and alcohol testing. Importing data is an easy task with resources to read “tab-delimited” text files saved from Excel spreadsheets.
Employers will often provide their active personnel/employee lists in an Excel spreadsheet. It’s easy to save the spreadsheet as “tab-delimited text” for the import into the Employer’s account. The system has the capability to import far more than the employee’s first name, last name and employee ID whether it is a Social Security Number or other proprietary employment ID, however, this article will limit the discussion to just a few of the available keywords for import.
The first row of the spreadsheet contains “key-words” that identify the columns of data. The following keywords identify the columns for the employee’s first name, last name and ID, respectively:
- First
- Last
- ID_No
Sometimes a single column might contain the employees first and last name as “John Doe” or “Doe, John”
When a single column contains both names you can use these key-words in row-1 above those columns:
- LF-Name: when the name in the column appears as “Doe, John“
- FL-Name: when the name in the column appears as “John Doe“
Other useful items often imported are fields referred to as “Location” and “Misc” and use those keywords.
When the program generates the random selection, if either of those fields are populated for the eligible participants in the randomization, the report of participants selected will include that data.
The Excel spreadsheet must be saved as Tab-Delimited Text to remove all the Excel formatting and proprietary attributes before importing the data into the application. When the file is saved, close Excel because Excel prevents other applications from accessing the file while Excel has it open.
The import resource uses the Browse feature to locate and upload the data file. Before the import is executes, all existing participant records for the account are deleted, however, existing drug and alcohol tests associated with the participant are not effected, and the new participant records are ready for a random selection.
Other import methods are provided that do not delete existing personnel. The Merge features set the attribute for all personnel to Not-Active. If an employee with the same name and ID is imported, the existing record is set to Active or Not-Active pending on the status of the imported record.
Below is an image of the Excel spreadsheet with instructions to save the spreadsheet, as Tab-Delimited Text, the file that is selected for import:
You can download the instructional Excel spreadsheet Here.
How to Import the text file:
Tap the Clients menu to navigate to the application’s main page, search for the account for the import, and tap the personnel icon to open Personnel Management where you will find the link for importing.
1. Search for the Account:
This example searches for a company with the name that starts with heritage.
2. Tap the people icon to open Personnel Management for the account.
The people icon appears according to whether the account is DOT or Non-DOT.
DOT Account-
Non-DOT Account
3. From personnel management, under the section on the right, titled Options for this Account, you’ll find links to open the import module and a help module that outlines all the keywords recognized:
Import the .txt file.
Import Help – including keywords required for the import.
4. Tap the Import icon, and click the Browse button to locate the .txt file saved from the Excel spreadsheet. Choose the Overwrite option, and click submit.
Any keywords not recognized are reported and the import is halted. For example, if you misspelled First (for first name) or used FirstName as the keyword, the import will stop and the module will report the keywords it doesn’t recognize.
When the import is completed, it will report the number of personnel records that were uploaded.