Importing Personnel Data for Random Selection for Drug and Alcohol Testing

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.

One of the biggest challenges faced by TPAs is keeping employee rosters up-to-date for the random selection of personnel for drug and alcohol testing.  Importing data is an easy task with resources that read Excel spreadsheets saved as “tab delimited” text files.

Employers often have the capability to review their active personnel/employee lists in an Excel spreadsheet.  From there it’s easy to modify the spreadsheet to import the data into the Employer’s account.  The system has the capability to import far more than simply an 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 a subset of the possible fields 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 the following key-words in row-1 above that data:

  • LF-Name: when the data appears as “Doe, John”
  • FL-Name: when the data 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 a random selection for drug and alcohol testing, if either of those fields are populated for the personnel involved in the randomization, the report of employees selected will include those data items.

The Excel spreadsheet must be saved as “Tab-Delimited Text” to remove all the Excel junk 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.  Existing personnel records are deleted, however, any existing drug and alcohol tests associated with the employee are not effected, and the new personnel records are ready for a random selection.

Other import methods are provided that do not delete existing personnel. The Merge features makes all personnel Not-Active prior to the import and if an employee with the same name and ID is imported, the existing record is set Active or Not-Active pending on the status of the imported record.

Below is an image of the Excel spreadsheet with instructions for saving for import:
You can download the instructional Excel spreadsheet Here.








Comments are closed.