BYU Student Author: @TylerBooth
Reviewers: @Nate, @DylanKing, @Mike_Paulsin
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Python
Need a program? Click here.
Overview
You love your job. However, there are some tasks that are incredibly frustrating. One of your daily recurring tasks is to download a .csv file that contains a single transaction that has been split into 100 parts. Each part will be billed to a different unit within the company. Unfortunately, the billing software does not take .csv file uploads and is structured in a way that does not allow you to copy and paste the data from other sources. Essentially, you have to manually type the 100 rows of data into the billing webpage. Every. Single. Day. The task is time consuming and it is very easy to make an error. If there is an error, it is even more time consuming to figure out where the error occurred.
You have decided that enough is enough. You are going to figure out how to create a Python script that will automatically and perfectly enter the data for you. You expect that the script will save a significant amount of time each day. All you have to do is take 30 minutes to write and test the script.
Instructions
To complete this challenge, you will need to:
- Write a Python script that will quickly enter all of the data from the .csv file into the webpage.
- For purposes of this challenge, an .xlsx workbook has been formatted to imitate the webpage. You must manually set the cursor in the starting position as this is also required on the webpage.
- Next, run the Python script so that the data is entered in the space provided in the .xlsx file.
- Make sure that the check figure turned green and the variance is zero. If it is not, find the problem in your script and try again.
- If you choose to complete the optional step below, start a timer when you run the script and, in the space provided, enter the number of seconds, that it took the script to automatically type the data. Better yet, builder a timer into your script to keep the time for you!
Optional
- To see how much more efficient the Python script is, you can MANUALLY TYPE the data into the space provided in the .xlsx file.
- Remember, the webpage does not allow you to copy and paste the data from the .csv file, so don’t do it here.
- Start a timer when you begin and, in the space provided, enter the number of seconds that it took you to manually type the data.
- The manual data entry is monotonous, but do your best. Capturing this information is important for showing your supervisor how much time will be saved through automation.
- For additional information, there is a check figure in the .xlsx file that will turn green if you entered the data correctly. If you did not, there is a variance total at the bottom of the data column that will tell you how off you are. There is no need to spend a significant amount of time making sure the data is manually entered in perfectly as this is intended as a rough demonstration.
Data Files
Suggestions and Hints
I chose to import three libraries that you may also find useful:
- time: Delaying the script can give you time to manually set the cursor in the proper position.
- pandas: This can be an effective way to read and work with the data in the .csv file.
- pynput.keyboard: This can help the script quickly navigate the webpage (.xlsx file). This library can execute keyboard commands such as typing words or pressing buttons.
- Import this library with: ‘from pynput.keyboard import Key, Controller as KeyboardController
- Create variable: ‘keyboard = KeyboardController()’
- To type use: ‘keyboard.type([Insert value to type here])’
- To press keys use: ‘keyboard.tap(Key.[Name of key])’
Solution
Solution Code
# Import Libraries
import time
import pandas as pd
from pynput.keyboard import Key, Controller as KeyboardController
start_time = time.time()
# Declare Variables
keyboard = KeyboardController()
location = input('Paste the file path here: ')
# Delay script to give user time to manually set starting place
time.sleep(3)
# Set the dataframe
df = pd.read_csv(location)
# Loop through the dataframe while pressing "Enter" after typing the data
for i, row in df.iterrows():
keyboard.type(str(row['Cost']))
keyboard.tap(Key.enter)
# Confirm to the user that the script is complete
end_time = time.time()
elapsed_time = end_time - start_time
print('Script Completed in: '+str(round(elapsed_time,2))+" seconds.")
Challenge123_Solution.txt
Challenge123_Solution.xlsx
Solution Video: Challenge 123|PYTHON – Save Time. Automate!
