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