Employee Training Reminders with Excel Files and Python

Accessing Excel files with Robocorp is much more convenient, faster, and robust than interacting with the Excel application UI. No Excel licenses or installation required. No flaky UI surface automation. Benefit from all the power and flexibility of the Python ecosystem!

Background story

Acme Corporation requires employees to complete the required training. The employee and training information is stored in a highly-sophisticated HR solution: Excel files!

Employees

The employees.xlsx Excel file contains all the people working for Acme Corporation. There are active and inactive employees, contractors, and managers. Each person has a unique identifier (Person ID).

Person ID	First name	Last name	Email	Category	Status
1	Michael	Michaelson	michael@example.com	Employee	Terminated
...	...	...	...	...	...

Trainings

The trainings.xlsx Excel file tracks the training information. Every time an active employee completes a training, a new row is added to the file. The Person ID is used to refer to the person in the employees.xlsx file.

Training name	Person ID	Date
Basics of RPA	1	2007-01-24
...	...	...

The process

Jane from HR sends weekly reminders to active employees who have not completed all the training. Managers and contractors are not required to take the training. Reminding people is a fully manual process, and to be honest, quite dull.

Example training reminder email

Hi, Suzy Smith! Remember to complete these trainings: {'How to eat underwater', 'Basics of RPA'}.

The automation

This Python robot automates the training reminder process:

• Reads the employee and training data from the Excel files. No Excel application required! Accessing the Excel files is much more convenient, faster, and robust than interacting with the Excel UI.
• Finds the active employees (Category = Employee, Status = Active).
• Sends an email reminder to all employees who have not completed all the required training.

The robot is built on top the open source stack for simplifying automation by Robocorp.

The robot consists of some configuration files (provided by the automation stack) and some Python files (implemented by the automation developer).

task.py

The robot reads the Excel file paths from environment variables using the os.environ function from the Python Standard Library.

The RPA.Excel.Files library can read and manipulate Excel files directly. You don't need to have the Excel application installed at all! The robot uses this library to read the employee and training information from the Excel files.

The robot uses the RPA.Tables library to read and filter the data (finding the active employees, collecting all the training names).

emailer.py

The emailer.py implements the email integration. The RPA.Email.ImapSmtp library provides the emailing functionality.

The RPA.Robocorp.Vault library handles fetching the email credentials in a secure way.

Configuration

You need to provide your Gmail credentials so that the robot can log in.

See How to work with email using Gmail and RPA Framework for how to configure Gmail for robot access.

Create a vault.json file for the credentials

Create a new file: /Users/<username>/vault.json

Point devdata/env.json to your vault.json file

I want to learn more!

Visit Robocorp docs to learn more about developing Python robots to automate your processes!

Robocorp portal contains many example robots with all the source code included.

Follow the

for automation-related videos.

Visit the Software Robot Developer forum to discuss all-things automation. Ask questions, get answers, share your robots, help others!