Fetching Data in UiPath

There’s just no way around it, sometimes we need humans to answer questions. The most straightforward way to get an answer from a human is to present them with some kind of a form, and wait for them to hit submit. Humans can handle very simple forms, but are unfortunately very error-prone, which must be anticipated and handled. Input screens must be presented in the simplest way possible.

Another source of data is the computer itself. Computers are good at knowing the current date, time, timestamps, starting a timer etc. You can fetch system information such as the current user, file information, and the current environment variables. This is normally done through code, which you can assign to a variable. Here are a few VB examples.

There are many advantages to using spreadsheets to store records of data. Basically a stand-alone table, a spreadsheet file is easy to read, understand, update, and share. Ubiquitous and user-friendly, most users today already have the skills to work with spreadsheets. Spreadsheets are ideal for smaller tables, and for projects where the user needs to see the data. Spreadsheets do have their drawbacks, however. There are data limits with spreadsheets, and they are not a good choice for big data (over a million rows). There are several methods in UiPath to fetch data from spreadsheets.

When fetching data from a spreadsheet, UiPath Activities return the data tucked inside a DataTable Object variable, which has its own syntax. Learn more. Care must be taken to validate the data, to ensure that data is not missing and is in the correct format and type. Sometimes the data must be manipulated to get it into the correct type. The data type of each column is automatically set when data is returned (usually a string or general), but you can loop through the DataTable variable and convert the data using a conversion function. Use the Invoke Code Activity and pass in the DataTable variable as an argument. Here’s an example:

'to open data table and loop through
dtMyTable.AsEnumerable().ToList().ForEach(
   Sub(rs)
      rs("Date")=CDate(rs("Date"))
   End Sub
)

Instead of the above code, however, I highly recommend a package called BalaReva.EasyDataTable.Activities. It contains several Activities to make manipulating data in a DataTable variable much easier. It gives the developer the ability to view the table after it’s imported, and convert the data type of a column if necessary. Learn how to use it here.

Email has been around a long time, and there are now a myriad of protocols, applications, and methods used to deal with it. It can all get a bit confusing. For now, let’s focus on the most common email applications, and the latest, easiest ways to fetch a bunch of them.

Once you get a list, you can loop through it and fetch the details of each email, such as the Subject, Body, Sender, etc. You can also see if there are attachments and download them as well.

UiPath can extract text from various word-processing documents, such as Microsoft Word, Google Docs, and PDFs. Usually only the text is imported, not the formatting. When using OCR to extract text from images you must select an OCR Engine.

Extracting data from websites, also called data-scraping, web-scraping, or screen-scraping, is kind of UiPath’s thing. You can extract text, attributes (including image urls), and tablular data from any website using UiPath’s UIAutomation.Activities package. Start with the Use Application/Browser Activity to set the URL. Save it to a UiElement variable (like uiOutput) for use later.

Databases are specifically designed systems used to store, organize, and provide access to data. As the Internet has evolved to offer more and more information, databases have also evolved to store and retrieve massive amounts of data using different structures and methods. Databases range from Access or Foxpro sitting on your computer, to BigData tables that power Google. There are relational databases and unstructured, or NoSQL, databases. Most databases are secured, and read access must be granted by the database administrator in order to retrieve data. Each type of database uses a specific driver. A driver, or provider, is a translation software that sets a communication protocol between the requestor and the database. Sometimes it’s necessary to download the driver. Then, a connection string is created that holds details such as the driver to be used, the name of the database, username and password.

In UiPath, database Activities can be found in the UiPath.Database.Activites package. Some experimentation may be required to connect to your specific database. UiPath offers a connection string wizard to help with building and testing the connection string, and includes many standard drivers. Once connection is established, the Run Query Activity is used to tell the system exactly what data you want, using standard SQL such as “SELECT CustomerId, CustomerName from Customers”. All data is returned as a DataTable variable.

Most enterprises have their own database servers, with a dedicated team to manage them. But in lieu of that, UiPath also offers a way to persistently and securely store data on their servers, using something called the UiPath Data Service. Useful for shared data that various processes can access, Data Service has its own browser interface, enabled through Admin. Through this UI, a developer can set up tables, called Entities, with fields and relationships just like a database. There are limitations, of course, in data storage capacity and call limits for each tenant allowed under license. But this can be a useful option in some use cases, like testing, experimentation, or for working out the schema kinks before bothering the database team. Unlike queues, the data is persistent and not deleted after processing. So this could also be useful for holding relatively unchanging data, like a lookup table of country codes, for example.

Unlike regular databases which need a connection string, Data Service uses the connection to the tenant already present in Studio. When an Entity has been created in DS, a Manage Entities option will appear on the Design Ribbon of Studio. Here you can see all the Entities that have been set up. Data can be added, updated, or deleted in the Entities through the Data Service UI directly, or through Studio Activities. All the activities needed to work with Data Service activities are in the UiPath.DataService.Activities package. For the purpose of this post, we’ll concentrate on the activities used to fetch data that is already there.

Use the Manage Entities pop-up and select the Entity you are interested in, and its namespace will then appear in the Project panel under Entities. Entities are now a separate data type (an object), which you can use throughout your process. The field names will even be seen in intellisense dropdowns, which is really cool.

Note that there is no SELECT clause using the data service. The complete object is retrieved each time, with all the fields except any files stored in each record. For that, you need to download the file separately. The ID of each record is completely unique, looking like gobbledegook, of data type GUID. But it is useful to use as a key in other systems so the complete record can be quickly fetched from the Data Service.

API stands for Application Programming Interface, and is a way to get just the data out of a website, without the presentation layer you see on your browser. Think of it as a back door to the data. API programmers create several ways in, called endpoints, which receive calls to their server directly, and return just the data. Programmers use APIs all the time to refresh their content, to update a map, to save something to the database, etc. Some APIs are private, and allow access only if you have a special authorization key of some kind. Others are open, and allow anyone to make the call.

There are two approaches used for APIs: SOAP or REST. The main difference is the SOAP (Simple Object Access Protocol) returns data in XML format, while REST (Representational State Transfer) can return the data in JSON, HTML, XML, or plain text. Of these JSON is the most common. Websites almost always have API instructions published on their site, which gives other programmers the details of their endpoints, formats, and data returned. It is important to review these instructions to set up your call correctly. An API call is basically just a URL string. For example, if you copy and paste this into your browser, you’ll see data returned.

https://reqres.in/API/users?page=2

To make an API call in UiPath, you need the package UiPath.WebAPI.Activities. The HTTP Request Activity has a configure button that launches a form where you can enter the endpoint, the authorization key if needed, and different parameters, and test the returned data. Returned output is split into the status code (an integer – 200 means success) and the returned content (a string in whatever format).

Once received, the content string must be parsed into something useful. This is called deserialization. You can then drill down into that object to pick out the data you need. For example, the above API call returns this JSON-formatted string, lets save it a variable strOutput.

{
"page": 2,
"per_page": 6,
"total": 12,
"total_pages": 2,
"data": [
{
"id": 7,
"email": "michael.lawson@reqres.in",
"first_name": "Michael",
"last_name": "Lawson",
"avatar": "https://reqres.in/img/faces/7-image.jpg"
},
{
"id": 8,
"email": "lindsay.ferguson@reqres.in",
"first_name": "Lindsay",
"last_name": "Ferguson",
"avatar": "https://reqres.in/img/faces/8-image.jpg"
},
{
"id": 9,
"email": "tobias.funke@reqres.in",
"first_name": "Tobias",
"last_name": "Funke",
"avatar": "https://reqres.in/img/faces/9-image.jpg"
},
{
"id": 10,
"email": "byron.fields@reqres.in",
"first_name": "Byron",
"last_name": "Fields",
"avatar": "https://reqres.in/img/faces/10-image.jpg"
},
{
"id": 11,
"email": "george.edwards@reqres.in",
"first_name": "George",
"last_name": "Edwards",
"avatar": "https://reqres.in/img/faces/11-image.jpg"
},
{
"id": 12,
"email": "rachel.howell@reqres.in",
"first_name": "Rachel",
"last_name": "Howell",
"avatar": "https://reqres.in/img/faces/12-image.jpg"
}
],
"support": {
"url": "https://reqres.in/#support-heading",
"text": "To keep ReqRes free, contributions towards server costs are appreciated!"
}

The UiPath Activity Deserialize JSON accepts as input a JSON-formatted string, and converts it into a JSON object. So we enter our previous variable, strOutput, and save the converted output to a new variable. Lets call it objJSON.

Now, we can actually get our data:

Single value

objJSON("total")

Single value embedded

objJSON("support")("url")

Assign Records into a DataTable Variable

In Javascript, arrays are enclosed in square brackets, and objects are enclosed in curly brackets. We can see from the result above there is an array called “data”, which has a list of objects. So let’s take a shortcut and save just save these records into a DataTable variable using an assign activity. The method we will use is found in the newtonsoft.json namespace, so make sure this is imported into your project.

newtonsoft.json.JsonConvert.DeserializeObject(Of Datatable)(objJSON("data").tostring)

We can see from the API Calls section above that a bit of programming is required to get the data into a format that we can use. It is necessary to read and understand how each API has been exposed, and how the call must be crafted. Plus, the API can change, which might break any program that calls it.

To try to make it easier, UiPath has created its Integration Service. Here, the most common 3rd-party APIs are pre-programmed into objects called connectors. As of this writing, there are 144 connectors in UiPath’s Integration Service and more being added all the time. There are connectors for sites such as Microsoft One Drive, Microsoft 365, Salesforce, GMail, Google Drive, AWS, PayPal, Slack and many others. It is up to the programmer of the connector to make sure it is kept up-to-date with the latest API. This should solve any breaking issues, but keep in mind that when using the Integration Service, you are outsourcing its maintenance.

Integration Service handles several things for you: the connection credentials, the API call, and the return format of the data. Another cool feature is triggers. Triggers open up a two-way communication between the site and your automations. With triggers, the 3rd-party site itself notifies UiPath that some event has happened, say an email has been received, which you can subscribe to and then launch an automation. The trigger passes the notification to your process in the form of arguments. In this example it would send the objectID of the incoming email, not the email itself. In your process, you could then fetch the email by its objectID.

To use Integration Service to fetch data in Studio, the activity package corresponding to the IS connector must be installed into the project. Most of the latest UiPath official packages are written to correspond with the latest IS connector. Details can be found in each individual connector documentation. Keep in mind that connections are folder-specific. So you could theoretically have different credentials for different folders. So make sure that your Studio instance is pointing to the right folder when you use Integration Service activities.

In Orchestrator, UiPath has folder-specific storage areas reserved for holding data for use in processes. Unless limited, users and robots with access to the folder will be able to access this data. There are 3 types of data storage available: Assets, Storage Buckets, and Queues.

Assets are key-value pairs stored at the folder level. Each asset has a name (a key) and a value that can be text, boolean, or integer. Another special type of asset is the Credential Asset, which stores both the UserID and Password. All assets are encrypted. Assets are set up in Orchestrator with their values, although can be set from within the process as well.

Storage Buckets are basically just an online, shared folder used to organize files that many processes can use as input. Any files, such as datasets for Machine Learning, Excel files, PDFs, even testing data files can be stored here. Storage Buckets are set up on a per-folder basis, and usually have the same permissions as the folder. Files are uploaded to them from within Orchestrator. Txt files can be read into a process, but most files must be downloaded for further processing depending on its type.

Queues are a special type of storage used for processing records of data. Queues are set up in Orchestrator with a specific schema. Once a queue is set up, a separate process called a Dispatcher is in charge of loading up the queue with the records. Then, another process called a Performer actually fetches these records from the queue to process them. One advantage of using Queues is built-in validation and error handling. Each record is tracked whether it has been processed successfully or not. Thus Queues are useful for large data sets, where something could go wrong, and can failures can be handled and reviewed. Queues can also be accessed by more than one machine, which can distribute the load of processing the items in the queue. NOTE: Queues are not to be used like databases, with updateable fields. They are intended as read-only, process-once then delete transactional processing.

For this post, let’s assume that the resources already exist in Orchestrator and let’s focus on the fetching activities used for each type. All these activities exist in the Core package.

Document Understanding is UiPath’s term for processes that use Machine Learning technologies to figure out what kind of document it is (classification), and then extract the data from it (extraction). Document Understanding is a whole specialty within UiPath, and beyond the scope of this post. You can read more about it here. Whether the data was extracted by a custom-built DU process, the online DU service, or the Document Understanding Template, the resulting data structure is familiar, a good old DataTable, which can be exported and manipulated like any other UiPath DataTable object (see Spreadsheets).