Breaking News

Read Excel file with UiPath

In the following article, I will show you step-by-step on how to read an Excel file with UiPath.

Business use case

Let’s imagine the Human Resource Department sent you an email requesting a new report with all employees from the IT Department who started in the last 6 months. The data will be extracted from an Excel file (attached to the email) containing the following list of employees:

First NameLast NameEmail AddressJob TitleDepartmentDate Started
JohnHarrisjohn.h@mail.comSenior C# DeveloperIT04/01/2019
LindaMileslinda.m@mail.comArchitectIT23/05/2018
OscarLeeoscar.l@mail.comMarketing & PR SpecialistSales14/03/2019
EmmaScottemma.s@mail.comBPM SpecialistIT10/11/2016
EdyFisheredy.f@mail.comRPA SpecialistIT23/02/2019
MonicaJonesmonica.j@mail.comHR ManagerHR18/08/2014
Read Excel file with UiPath

Solution

First, let’s open the UiPath Studio and create a new process.

Create a new process

Drag and drop a Sequence component and rename it Read Excel.

Add Read Excel Sequence

Go to Application panel and search for Excel Application Scope. The container opens an Excel workbook and provides scope for Excel Activities.

Excel application scope

Click on the 3 dots (…) from the Excel Application Scope to select the excel file. In my example, the file is located in the project folder. So the relative path was used. In other cases, the full path might be required. Also, the path can be dynamically changed using a string variable.

Configure the Excel Application Scope

To read the content of the file search for the Read Range activity. Drag and drop it inside the Do container. The Range parameter is empty by default which means the entire sheet will be read. A range can also be provided (“A2:Z100”). The outcome of this activity is a DataTable. We will use the outcome to perform the required data manipulation.

Read Sheet1 from the excel file

On the Properties panel look for the Output > DataTable. Use the shortcut CTRL+K to create a new DataTable variable. Let’s call it dtEmployee.

.

Configure the output for the Excel Read Range activity

Go to Variable panel and change the scope of the new variable to Read Excel.

Change the scope for the DataTable variable

Now, to display the content of the dtEmployees use the Output Data Table activity. It converts the DataTable type variable into a string. Save it into a new string variable called EmployeeData.

Convert DataTable to String using the OutputDataTable activity

We can easily use the Log Message (Trace level) activity to display the new string variable EmployeeData and test everything out.

Display the message in the log system
The Output

So far everything looks perfect. We manage to read the data from the Excel file. The first step was successfully completed. The second step is to apply the required filters: only the employees from the IT Department and the employees who started in the last 6 months.

In order to apply the filters we have to use the Filter Data Table activity.

Search for the Filter Data Table activity

Drag and drop it between the Read Range and the Output Data Table as in the image below.

Place the Filter Data Table after the Read Range activity

Select the Filter Wizard to configure the filter. Enter dtEmployee as the Input DataTable. Do the same for the Output DataTable. This will over write the variable with the new filtered data.

Add the 2 filter conditions. The Department has to be equal to “IT” AND the Date Started has to be greater or equal to a date 6 months ago.

Configure the Filter Data Table activity

When I created the dDate variable (DateTime data type) I also assigned the following default value: DateTime.Today.AddMonths(-6).

DateTime.Today returns the current date and the AddMonths method adds a specific number of months to the specific date. In our case we want to subtract 6 months.

The default value

Now run the automation. You should see only 2 employees: John and Edy. (Bare in mind that in my case the automation was executed on the 3rd of May 2019. Running the automation on a different date will bring different results)

The output

Download the solution

Click on the button below to download the source of the demo (Read Excel file with UiPath).

Download the UiPath Automation

About Valeriu B

Leave a Reply

Your email address will not be published. Required fields are marked *