TagUI for python application use case

Process Automating to download a SSRS report from a website using TagUI for python.

Yes, TagUI is available in both Human Language and for Python. How awesome is that right! Thanks to Ken Soh, who is the creator of TagUI RPA tool and for bringing it for a programming language like Python. Though it is not much different from the normal one.

The objective here is to use TagUI for python (called as RPA for Python) to download a SSRS(SQL Server Reporting Services) type report from a website. Then we can make it as a standalone windows application or schedule it to run at a particular time everyday.

Downloading & Installing TagUI RPA for python

The element’s xpath has to be mentioned just like the normal TagUI. For getting xpath for a particular element, right click on that element & copy the highlighted part from developer tools. Or the easiest method is to download a chrome extension called SelectorsHub, right click on element to find SelectorsHub > copy relative xpath

Use pip install rpa to install the tool in a command prompt or anaconda environment. And using import rpa as r can import the library.

import rpa as r
#created a csv file with usernames & passwords
#converted into a dataframe
df = pd.read_csv(<insert path to csv>)
df['txtUserName'] = df['txtUserName'].astype(str)
r.init()    #initiates the RPA process
r.url('<insert url for the website>')
#types username & password incase to login with accounts
#used for-loops incase to login with multiple accounts
#other wise no need if a single user login
for i in range(len(df.axes[0])):   
   r.type(‘<usrname_xpath>’, df[‘txtUserName’][i])  
   r.type(‘<pwd_xpath>’, '<insert password>')  #mention the password
   r.click(‘btnSignIn’)                #clicking 'Signin' button
   <code to navigate the website>   
   r.click(‘lnkSignOut’)
r.close()                              #closes RPA process

For a single user login, it will be much simpler like below:

r.init()                                  #initiates the RPA process
r.url('<insert url for the website>')
r.type(‘<usrname_xpath>’, ‘<insert UserName>’) #mention username  
r.type(‘<pwd_xpath>’, '<insert password>') #mention the password
r.click(‘btnSignIn’)                       #clicks 'Signin' button
   <code to navigate the website>
r.download_location('<insert folder path>')#downloads to this folder   
r.click(‘lnkSignOut’)                      #clicks 'Logout' button
r.close()                                  #closes RPA process

Now, the downloaded file is in pdf format. Hence, to extract data from a PDF, tabula library was used as the data is in tabular format.

Extracting & reading tabular data from a PDF:

Use pip install tabula-py to install the library. Note tabula also need java to be installed in the machine. Recommended to check tabula website for any troubleshooting & getting started.

dfs = tabula.read_pdf("test.pdf", pages='all')

After extracting & reading the data to a data frame, done necessary data preprocessing on the data frame like concatenating tables, renaming column names, rearranging columns in the required format. The data is now ready to be pushed to a database.

Pushing data into a Database:

For simplicity and convenience, SQLite 3 database has been used. First create a table in the database. Then to connect the SQLite dB table to push data, use below code…

db_conn = sqlite3.connect(<path to db file>)
c = db_conn.cursor()
df.to_sql('<table_name>', db_conn, if_exists='append', index=False)
db_conn.commit()
db_conn.close()

Now that the data has been pushed to the DB, the job is done. But this process has to be done every working day. Which is so boring, instead we can use windows task scheduler to automate this task.

Scheduling a task using windows task scheduler:

First, have to create a batch file .bat is the extension. For that, all we need are the location of the python installed in the machine, location of this task script. Open a notepad and insert both of them and in the next line add pause so after the process gets completed, the command prompt will stay else will closes. (Which helps us to see any errors occurred while process is being executed). Save it as .bat file. Below is the format of the batch file…

<insert path for python location> <insert path for the script>
pause

Next step is to open the windows task scheduler, click ‘Create Basic Task…’ under ‘Actions’ tab on the right hand side.

Give a name to the task, provide a description, click next. In ‘Triggers’, choose a trigger like daily, weekly, monthly etc. Under ‘Actions’, choose start a program click next. Browse to the batch file created earlier in the ‘program/script’. Arguments & start in can be left blank. (provide the folder location of the batch file in start in if it not works). That's it…click ok

Finally, the collecting data from a website, preprocessing it, pushing to Database, scheduling has been done completely.

For this use case, scheduling the task has been chosen, but a standalone windows application can be made using auto-py-to-exe for converting .py file to .exe file.

Its so simple to use, Provide the path of the .py script which has to be converted. With many options like to get ‘one directory’ or ‘one file’ for the application, providing an icon, downloading the .exe file to desired location, this library makes it intuitive with an interface.