How to connect database in UIPath?

     In most cases, most of non-IT employees have no  privileges to connect databases in the company. But there is no one who can deny that data is becoming more and more important in our works even lives. So as long as we have the opportunities to use database, please seize it. You will find a new world of it.

    In my experience, I use UIPath to connect with the databases like mysql, which is easy installed in our personal computers, and oracle which is more complicated than mysql.

    On the whole, to connect different databases in UIPath is very similar. In some cases, we need to do other things to make it work.

    Now, I will show you how to connect mysql database in UIPath:

1, download database package from Manage Package in UIPath.

2, install ODBC connector for the database, then add ODBC data source in Windows tools.(notice the connector is 32-bit or 64-bit which decides the ODBC bit)

3, backing to UIPath, add Connect to database activity, then choose the ODBC source added.

4, after connecting database successfully, we can run sql query to retrive datatable. What's more, we also can pass parameters to sql query using "?" . If pass many parameters to sql, just adds them in order. As below:

There are 2 extra points I want to add.

    1, In the newest version of UIPath, when using Insert activity to add records into database you will get an error. If you use the old version UIPath like 21.10.5 run the same activity, which will work perfectlly.

    2, If you want to connect Oracle database, configure tnsnames.ora file first. Then copy the file into project folder. Finally, it works.

 

How to use datatable in UIPath?

    In our daily work, no matter what your job is, it is common to do with datatable inevitablly. Sometimes we have to fill it, and sometimes to transform it.

    In my view, datatable is so complex that many of us will be aukward because datatable is usually related with data model or data warehouse. But today, we are not gonna to discuss data model or something intricate. We only focus on datatable using in UIPath. And in UIPath, we have several ways to do it: inner-built activities, VB.net code or python  and so on.

    Now, I will share the most efficient and simple way I have been using.

1, Inner-built activities and VB.net

    Firstly, let's have a look on inner-built activities list showing below. Using them we can creat, update, filter, clear datatable easily. So I don't want to introduce them one by one tediously. Instead, I will make you to know the limits of easy way and use others.

    Such as to add a calculated column in datatable, to remove duplicate rows by multiple columns, to pivot and unpivot and so on. Before we dive into it, let's use some more efficient way, VB.net , to replace inner-built activities.

2, VB.net code

    There are several VB.net code we can use in Assign activity.

    1, In order to insert a new row into datatable, we need to use key word 'New' to state a variant, then put the value into the container. At last, use Add Data Row activity to add the row into the datatable.

    2, We can change column name using the following way.

    3, Datatable has index, of row and column, we can use it to get one value of it.

    4, And convert column type as well.

dt.columns("col1").datatype=gettype(system.datetime)

    5, At last, I will show you 2 scenarios using Invoke Code activity which is really burning me out owing to intricate code grammar. Therefore, I highly recommand to use python which I will introduce it at the end.

    NO1, to replace values in one column, we can use the code in Invoke Code activity as below:

        

dt.AsEnumerable.ToList.ForEach(Sub(row) row(2)= row(2).ToString.Replace(Chr(34),””)) 

    NO2, to add calculated column into datatable is like NO1 method:

        

dt.Columns.Add("asdd",gettype(Decimal)).Expression="[col1]+[col2]"

3, Python pandas

    I think VB.net code is hard for me when the code is too long. As I know, both R and Python have powerful packages to solve the problem described previously. Unfortunately, new version of UIPath don't support R script now as R package is outdated. For this reason, I could only use Python. Actually, I am not good at python so I won't give script examples but I can share a script I am using and a useful blog which introduce how to use pandas with code examples.

    There is one important thing that we need to keep in mind when use python script in UIPath that json string is a bridge between datatable and dataframe. In other words, if we want to pass a datatable parameter into python script or deliver the output of python script, which is a dataframe type, we need to transform the parameter into json string type first before we get the final data type. 

    This code can help us convert datatable into json string using Assign activity:

    

a=jsonconvert.serializeobject(dt)

    Then use the following python script to subtotal datatable by column "A": 

        

import pandas as pd

import json

                def add(a):

        data = json.loads(a)

        df = pd.DataFrame(data)

        df = df.groupby("A").agg('sum')

         df = df.reset_index(drop=False)

        return df.to_json(orient='records')

    Any other functions of pandas, you can browse this website: Python Guides - Statology

Blog Introduction

    I am DonFeng. As an accountant in China. 

    Although I hate numbers when I had to copy ,transform and paste Excel datatables at daily work. And of course I had made so many mistakes as I felt like a robot but actually not.

    Later, not only have I learn PowerBI and UIPath, but also a few knowledge about sql, json and etc which help me to use BI and RPA tools well.

    I always remember a glossary of economy, my major at uni, "Learning by Doing". I learn from the internet where so many talented guys share their knowledge and experience. Then I use it at work to optimize my efficiency and effectiveness.

    From now, I want to share my skills as a role of business analyst not an it programmer such as using RPA to coordinate with other deparment colleagues, using RPA without Orchestrator to manage robots, and how to combine PowerBI to help manage process/ business management.

    Stay tuned!