RPA and Python

Free of charge Python RPA Automations. Due to the COVID crisis, some friends asked me to offer them an inexpensive solution to create a robot that would carry out some processes. The video shows how Python can be used to create robotic processes.

Mr. Cohen
November 10, 20208 min read

Source Code: https://github.com/y0d32/rpa_n

Why RPA and Python?

Due to the COVID crisis, some friends asked me to offer them an inexpensive solution to create a robot that would carry out some processes. The video shows how Python can be used to create robotic processes.

Let's Get Started!

In general, RPA frameworks can accomplish such tasks, but they cost money. You have to pay for a license, which is sometimes expensive ... Due to the global pandemic, some businesses have real staffing problems, whether they are attrition or there are situations where employees can no longer work due to illness. In the video you see a case study, a free of charge solution implemented in Python, to create a simple RPA process. #Python #RPA

import os
import subprocess
import time
from selenium import webdriver

import pandas as pd
from datetime import datetime

from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By
from selenium.webdriver.support.select import Select
from selenium.common.exceptions import NoSuchElementException
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait

First you have to do the imports. Like all the other languages you have to import all the packages you need to work with, before you run the code. Therefore we import selenium, os, time and the webdriver, which is basically the robot that will do all the clicks and checks for us!

FPATH='/Users/right/Desktop/geckodriver.exe'
SITE='https://ec.europa.eu/taxation_customs/vies/vatRequest.html'
FILE='/Users/right/Desktop/vat_codes.xlsx'

Then we have to set the path for the firefox geckodriver. I've used firefox, you can use chrome or even IE :). I've declared the rest of the constants for the site and for the excel import. The file can be downloaded from github and then you can re-set the FILE constant to the correct path.

Pandas Data Frames

df = pd.read_excel(FILE)
df.columns=['VAT', 'Status', 'Date']
df['Country']= df['VAT'].astype(str).str[:2]
df['VATID']= df['VAT'].astype(str).str[2:]
print(df)

Like all the other RPA robots that uses queues instead of queues we will use a data frame that will be stored into the memory, before pasting all the stored data to the website. The we do the rest of the head.

country=df['Country'].tolist()
vatid=df['VATID'].tolist()

# print(country)
# print(vatid)

Components

We need to make a function that will give us a variable with a value from the dictionary object from bellow. We simply need the values 6, 13, 26 in order to get the right value from the drop-down list. Than we declare the rest of the variables that we need in order to run the code. i will be used to track the status and in the code it will be declared as a global variable.

def switch_demo(argument):
    switcher = {
        "DE": 6,
        "GB": 13,
        "SE": 26,
        "DK": 7,
        "CY": 4,

    }
    #print (switcher.get(argument, "Invalid month"))
    return switcher.get(argument,"Invalid month")

browser = webdriver.Firefox()
browser.get(SITE)
i=0
browser.maximize_window()

The body of the code has a try and catch and inside of this block of code the magic litteraly happens. Than based on XPATH you can select the elements you want to bend in order to make your automation. UiPath and BluePrism basically go even better to identify by spying all the elements in order to have bullet proof element identifiers that don't change over time. Is not ok to have the client comming to you after a coupld of months saying that the rocess is roten due to the fact that it can't click on different elements etc.

Now the entire code will look like this:

rpa_n.py

import os
import subprocess
import time
from selenium import webdriver

import pandas as pd
from datetime import datetime

from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By
from selenium.webdriver.support.select import Select
from selenium.common.exceptions import NoSuchElementException
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait


FPATH='/Users/right/Desktop/geckodriver.exe'
SITE='https://ec.europa.eu/taxation_customs/vies/vatRequest.html'
FILE='/Users/right/Desktop/vat_codes.xlsx'
df = pd.read_excel(FILE)
df.columns=['VAT', 'Status', 'Date']
df['Country']= df['VAT'].astype(str).str[:2]
df['VATID']= df['VAT'].astype(str).str[2:]
print(df)

country=df['Country'].tolist()
vatid=df['VATID'].tolist()

# print(country)
# print(vatid)

def switch_demo(argument):
    switcher = {
        "DE": 6,
        "GB": 13,
        "SE": 26,
        "DK": 7,
        "CY": 4,

    }
    #print (switcher.get(argument, "Invalid month"))
    return switcher.get(argument,"Invalid month")

browser = webdriver.Firefox()
browser.get(SITE)
i=0
browser.maximize_window()

def mymain():
    global i
    try:
        for (c,v) in zip(country, vatid):
            print("\n" + c,v)
            element = WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "countryCombobox")))

            select_c = Select(browser.find_element_by_id("countryCombobox"))
            nr = switch_demo(c)

            if not nr == "Invalid month":
                select_c.select_by_index(nr)
                time.sleep(1)
                el = browser.find_element_by_id('number').send_keys(v)
                time.sleep(1)
                btn_submit= browser.find_element_by_id('submit').click()
                time.sleep(3)
                """Check the Writing"""
                message = browser.find_element_by_xpath("/html/body/div[2]/div[4]/div/div/div[2]/div/fieldset/table/tbody/tr[1]/td/b/span")
                mymessage = message.text
                print(mymessage)
                # df['Status'].loc[i]=mymessage
                # df['Date'].loc[i]=time.Now()
                df.loc[df.index[i], 'Status'] = mymessage
                now = datetime.now()
                df.loc[df.index[i], 'Date'] = now
                """GO BACK"""
                btn_back=  browser.find_element_by_xpath("/html/body/div[2]/div[4]/div/div/div[2]/div/fieldset/p/a")
                btn_back.click()
                time.sleep(2)
                i = i + 1
                #print(i)
            else:
                msg="Incorrect VAT ID, please check the ISO country code"
                print(msg)
                df.loc[df.index[i], 'Status'] = msg
                now = datetime.now()
                df['Date']=now
                i = i + 1

                #print(i)

        print("\n Please find bellow the Data Frame\n")


        mydf= df.sort_values(by=['Status'], ascending=False)
        print(mydf)

        mydf.columns=['VAT codes', 'Status', 'Date', 'Country', 'VATID']
        mydf.to_excel(FILE, columns=["VAT codes", "Status", "Date"],index=False)

    except NoSuchElementException:
         print("The page did not open for 3 minutes")
         df['Status']="The site is down"
         now = datetime.now()
         df['Date']=now

    finally:
         #browser.quit()
         browser.close()
         print("\n That was it!\n Thank you for the challenge!\n")
mymain()

Feedback

Looking forward for your feedback! Please like, share or subscribe!

The video

You can find more info on Youtube here

My Video