Make your tech work for you!
Recently I was tasked with a mind-numbingly dull data population task which would probably take a day. These kinds of tasks should have been outsourced to machines years ago, anyone with a basic knowledge of programming could knock up an automated script for this kind of job in less than a day. If automating something takes a similar time to actually doing it – then it seems like a no-brainer to me!
Let’s jump into what I had to do:
- Read a spreadsheet with a list of engineering products from a manufacturer
- Go to manufacturer’s website
- Log in
- Search for a product
- Click download button
- Repeat for all products on the list
- Edit spreadsheet with notes to say what couldn’t be downloaded
As it requires importing data from a file and web scraping – it looks like a good job for Python. It’s very easily installed, and there’s a fantastic amount of supporting libraries available for Python, all that can be included with a terminal command and a single line of code.
Reading Your Excel Sheet
Let’s jump into what I had to do:Importing data from a file is easy, whether it’s in binary (Word, Excel) or in plaintext (CSVs, .txts), as there’s most likely a function in pandas to read it.
A quick search shows that there is!
We have to open the file then read it with pandas, which gives us a dataframe. Which is like an array, but with more labels, and multi-dimensional.
import pandas as pd with f.open("filepath") as file: df = pd.read_excel(file)
with syntax will close the file once the indented code has run. Now we have a dataframe to work with, let’s see what it looks like.
Let’s jump into what I had to do:
Here we see the manufacturer code, and the filename the downloaded file needs to be renamed to.
In order to automate the file downloads, we’ll use Selenium with ChromeDriver. This allows a headless version, so once we’ve developed the script, we can run it without opening a physical browser window.
from selenium import webdriver from selenium.webdriver.chrome.options import Options driver_options = Options() #driver_options.add_argument("--headless") #Leave this commented out for now, until script is finished driver = webdriver.Chrome('./chromedriver', options=driver_options) driver.get('google.com') driver.send_keys('test')
Automating the log-in Process
Finding HTML elements
Now, we most likely want to find some elements of the webpage to interact with. In this case we need:
- Login button
- Login username
- Login password
Since we can’t click these buttons or fields any more (we’re automating it!) we need a new way to refer to each of these elements. If you’re on a PC right now hit F12, that should bring up a console window of some sort. Now in Firefox it looks a little like this
If you click on the top left button, you can then hover over any part of the webpage. Right click on what you’re interested in and the inspector shows you the exact HTML element you clicked on.
In my case my publish button looks like this
<button type="button" aria-disabled="false" aria-expanded="false" class="components-button editor-post-publish-panel__toggle editor-post-publish-button__button is-primary">Publish…</button>
There’s a lot going on here, and I’m not going to tell you how to interpret HTML. Instead we need to look for part of this element that’s unique. I think the
post-publish-button__button class of this button element will be unique. Once you’re on a webpage you can search for an element by its class name (other identifiers are available):
els = driver.find_elements_by_class_name('post-publish-button__button')
If the publish button is the only one with this class, this function should return a list with only one value. We just click it with – you guessed it – the click() method.
if len(els) == 1: print('This button is unique!') els.click()
Now we’ve clicked the login button, we should get to the login page. Let’s do the same element finding process again, but with the login username and password inputs. We need to send these some data, our login and passwords. In this case there’s no identifying class name, instead let’s use their
<input class="form-control" type="Email" placeholder="Enter your email address" autofocus="" data-val="true" data-val-required="The Username field is required." id="Username" name="Username" value="">
login_fields = driver.find_elements_by_name('Username') if len(login_fields) == 1: login_fields.send_keys('email@example.com')
Now we’ve sent text to the login screen and we know how to click the login button – let’s put this all in a single function
def log_in(driver): user_fields = driver.find_elements_by_name('Username') pw_fields = driver.find_elements_by_name('Password') if len(user_fields) == 1: login_fields.send_keys('firstname.lastname@example.org') if len(pw_fields) == 1: pw_fields.send_keys('password') login_button = driver.find_elements_by_class_name('login-button') login_button.click()
We’ll look at combining this web automation with reading spreadsheets in the next part.
If you’ve got any questions send them to email@example.com