Hello, and welcome to UiPath Essentials training - Excel and data-tables.

In this tutorial you’ll learn the basics of working with excel files and spreadsheet

data.

I’ll show you how to open files... read cells or ranges... output data... and iterate

through a table to evaluate each row and take decisions.

Along the way you’ll pick up a few other tips&tricks, and subtle differences between

several ways of solving a problem.

You should already be familiar with the UiPath interface & workflow, passing variables between

actions, and if/else decisions, as we’ll use these concepts throughout this tutorial.

So, if you’re ready, let’s begin!

We’ll start off with the simplest workflow: opening a sample excel file, printing

its contents, and saving it into a new file.

Easy enough.

Most of the actions we are interested in are found here, under App Integration, Excel.

As always, we'll begin with a new sequence.

When working with Excel files, the first activity will always be the Excel application scope.

It is a container, and all the other Excel activities must go inside it.

Let’s choose a sample Excel file.

The browse button, by default opens in the location of the current project, and paths

are relative to this location.

The workbook path is automatically filled out, but please note that it is a variable

that can be dynamically changed if required.

Another important aspect of this activity is this Visible checkbox.

It tells UiPath to either read the file using Microsoft Excel, or, if it is unchecked, the

read operation will be performed internally, directly on the file.

The difference between the two is that the default, Visible option, requires Excel to

be installed, and all actions will be performed through it.

So you will be able to see in realtime what is going on, which can be useful for debugging,

or just checking the progress of the workflow.

If the Visible option is unchecked, you do not need Microsoft Excel to be installed on

the system, and the whole process happens in the background.

And that’s what we’ll use for the moment.

Next, we’ll add a ReadRange action.

It reads a portion of the Excel file and stores it in a data-table, where different operations

can be performed on it.

What’s the difference between a workbook and a dataTable you ask?

Well, a workbook is just a reference to an excel file; it can have all types of data,

formatting, sheets, layouts, merged cells, and multiple data-tables.

On the other hand, a DataTable, is just the simplest type of spreadsheet data: rows & columns,

with optional headers.

This simplified format is useful for further processing, as we'll see later.

So, we’ll create the output datatable, let’s say... “data-table 1”

The Range parameter is empty by default, which means the whole sheet will be read.

But we can specify a range as two colon-separated cells.

Moving on, let’s display it.

We’ll first add an OutputDataTable action.

Despite its name, the OutputDataTable does not print its content to the output pane,

it just converts it to a string that we can display properly.

Create a new output string.... and for the input data-table, choose the output of the

ReadRange action, DT1.

Then drop in a Message Box to display it.

Just fill out the text variable with the one created previously, so we can test everything out.

And... here’s our table!

Now all we have to do is save it to a new file.

And because it's a different file, we need a brand new Excel Application Scope.

For the Path we’ll specify the name of the file we want to write to, we’ll call it

Results; if there is no such file, it will be created on the fly.

And inside it, a Write Range action.

It’s very similar to the ReadRange action, except it needs the dataTable to write to

the file.

Oh, looks like it doesn’t recognise it, we probably need to change the scope of the

data-table to this whole sequence, “Read Write Excel”.

There.

It’s worth mentioning that if there is any

data in the excel file, WriteRange overwrites it, similar to a paste command in excel, starting

from the obvious, Starting Cell.

Let’s see.

The message with the data-table, and we should

see the results file appearing here too.

There it is!

You may have noticed that the information written in this file has headers.

If you want to exclude them, you’ll want to use the AddHeadders option.

Both in the Read action, where it signifies that the first row is indeed a header... and

in the Write Range, where you’ll deselect it if you want just the data.

We’ll delete the old results file and run.

And.. no more headers.

Now that we have a basic working project,

let’s see some other actions useful in working with Excel and DataTables.

Append Range is similar to writeRange, except it adds data after all currently existing

data, without overwriting.

And just like its sister activity, it takes a data-table as input parameter.

We could read from another excel file, but we’ll try something else: we’ll make a

new one.

The data-table actions can be found here, under Programming, data-table.

To make a new one we’ll drag in a Build data-table.

It has a simple user interface for customising your table.

It starts off with the default 2 rows & 2 columns layout, but we’ll delete them to

start fresh.

Columns also have types, and we have all the range of Visual Basic .NET at our disposal.

For now we’ll just leave it as string.

We’ll add a few more rows of data...... and we’re done.

We just need to give this new data-table a name, let’s say... new dataTable,

and pass it to Append Range.

We’ll delete the old result... And here are the 3 new appended rows.

As you can see, the number of columns in the appended datatable isn’t really important,

but it is advisable to keep the database well-organised.

Let’s see what else you can do.

If you wanted, you could also sort this list using the Sort Table activity.

The column name is pretty obvious, it’s the column we want to sort by; we’ll use

the the first column, called “year”.

The Table-Name parameter is different.

It requires a table defined in Excel.

So open the Results file and delete the existing content.

Then select the first 3 columns and go to insert, table, and hit ok.

And this is the table name parameter that you need; that you can change if you need

to.

Save and close this down, and back in UiPath,

paste the name of the table here.

And the last thing to do is make sure the headers are included in the Write action because

they are required for sorting.

And here’s our sorted table, with the headers we enabled in the writerange action.

Two more valuable actions are Read and Write Cell.

You can probably guess how they work, but let’s see a quick example anyway.

We’ll start with a new sequence and the usual Excel Application Scope, opening the

same excel file as earlier.

Inside it, add the Read Cell and Write Cell actions from the activities panel.

We’ll read from the C3 cell... and write in the E5 cell, both in the same file.

And take the output from the Read Cell and pass it to the Write Value action...

Like...so.

Looks like it worked.

Another action that you might find useful is the Select Range.

All it does is select the individual cells found in the specified

range.

Let’s say from...

A1... to F4. On its own the Select Range doesn’t have

a direct effect, but after selecting something, you can use it to delete, copy, move, or do

any other operations directly in Microsoft Excel.

Ok, let’s quickly review what we learned, because next-up we’ll take it a step further.

Excel Application Scope is a container for read & write actions that are performed on

a certain excel file.

ReadRange reads a portion of an excel file and outputs a data-table.

Output data-table converts a data-table into a string, usually for displaying;

and Write Range, writes a data-table into an excel file.

These are just the main ones, there are a few more actions found under App Integration,

Excel.

Things like Read and Write cell, Append, Select range, and others I’m sure you will discover

on your own.

Great.

Now that we have the basics down, let’s try a more practical example.

We have here a sample public employees database, with columns for Name, Age, Work Domain, Current

job, and Salary; it has around 300 entries.

We’ll take a look at how to extract, analyze and filter relevant information, that you

could then insert into your own accounting software, or mailing server, citrix, or whatever

else you may need.

We’ll start with the usual approach, an Excel Application Scope, ReadRange, and so

on, but this time we’ll try it in flowchart mode so you can visualise it better.

So, setup the Excel Application Range... and add a ReadRange to get the excel file

into a data-table called...

DT1.

Make sure the scope for the data-table is set to the parent Flowchart, we called it

“Filter Database”.

Next, we’ll add the important bit, the ForEach Row action.

We’ll use it to iterate through the whole data-table, row by row, and analyze each to decide if

we want to copy it to a new database.

Please note that there are 2 similar ForEach actions.

The one we’ll use is exclusively for data-tables, and the other is a generic one that can be

used for various types of lists and arrays.

Inside our ForEach action, we need to tell it which dataTable we want to iterate thru.

In our case, DT1.

To look at each value individually we’ll add a Get Row Item activity.

It outputs the value of a specific cell in the current row.

To indicate which cell we want, we can use an index (like for example the 4th cell),

or a column name, if we have Headers, which we do.

The latter is usually prefered, because it allows for more flexibility: if the excel

file is modified, with added or removed columns for example, the workflow won’t break, because

the names of the columns will indicate the correct data.

We’ll get the Name of the employee... and print it out.

Ah, it can’t find the column name, most

likely because we forgot to include them in the Read range activity.

Let’s see... yup, here it is.

Ok, so far so good, we have the names.

Since we want to filter based on the data in the table, we need to get the rest of the

data too, and analyse it.

In the forEach loop, we’ll duplicate the GetRowItem to get age and income.

We’ll just make new variables for them and indicate the appropriate columns.

And to make some decisions based on this data, we’ll put in an IF action, that you learned

about in a previous training, “Control Flow”.

Let’s see.... who is younger than 30....

AND with an income of more than 100k. And let’s display its name.

It looks like we have about 10 early achievers on our list :)

It looks like we have a few early achievers on our list :)

If you want to could print the rest of the data in the same WriteLine.

And add a bit of separation.

I’d just like to stop and point out that

this whole workflow, while fairly simple, is pretty powerful, because this WriteLine

is just a placeholder.

It’s the place in the workflow where you would normally add real-world functionality

to your project: using other UiPath features, you could insert this data into a web form,

inventory, your own custom app, citrix, and so on.

Or you could filter, sort, or otherwise process the data.

The possibilities and endless.

But so far we’ve only printed the data, let’s see how we can save all of it into

a new data-table for exporting.

We’ll start by going up one level to the main workflow, in the Excel Application Scope.

Add a Build data-table action right after the ReadRange; we’ll use it to store the

filtered datatable.

Inside it, delete the existing columns and create 3 new ones, for name, age and income.

We just need to give this new data-table a name, let’s say... new dataTable.

And make if visible from the parent flowchart.

Now here’s the important bit, inside the ForEach action.

Instead of the WriteLine action, we’ll use an AddDataRow.

The two important parameters here are the name of the data-table to add into –

in our case, newDT – and the data to add, in the form of an array.

The array is specified as a comma separated list, enclosed in braces – or curly brackets.

We’ll use the 3 variables that we read here with GetRowItem – name age and income.

So now, for each row in the file, the program will read the age and income, and IF the condition

is met, it will add the data to the empty data-table newDT, created earlier.

And that’s it.

We’ll just save the datatable into a new file, with an Excel Application Scope and

a WriteRange, the same way we saw earlier.

And here’s our Result file, with the filtered results.

This concludes our Essentials training for Excel and data-tables.

For a more in depth look at this topic, please see our Advanced training in this series.

See you soon!