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 Name||Last Name||Email Address||Job Title||Department||Date Started|
|John||Harrisfirstname.lastname@example.org||Senior C# Developer||IT||04/01/2019|
|Oscar||Leeemail@example.com||Marketing & PR Specialist||Sales||14/03/2019|
First, let’s open the UiPath Studio and create a new process.
Drag and drop a Sequence component and rename it Read Excel.
Go to Application panel and search for Excel Application Scope. The container opens an Excel workbook and provides scope for Excel Activities.
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.
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.
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.
Go to Variable panel and change the scope of the new variable to Read Excel.
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.
We can easily use the Log Message (Trace level) activity to display the new string variable EmployeeData and test everything out.
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.
Drag and drop it between the Read Range and the Output Data Table as in the image below.
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.
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.
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)
Download the solution
Click on the button below to download the source of the demo (Read Excel file with UiPath).