Reading from Excel

In this next workflow, we will pick up the Request Excel file from the folder we created, read the ticket data, and incorporate them in variables for the next workflow to process. This will help you get your feet wet with Excel-based automation:

1. Let's start by creating a new Sequence in the project. This will create a new workflow for us to work on:

2. Let's name it ReadExcelRequest and click Create. The studio will create a default sequence for you:

3. Within the workflow, let's first add the arguments using the Arguments tab at the bottom of Studio (refer to the following information box). Arguments will enable us to input and output data from this workflow to the main workflow. Proceed and create four arguments, as shown in the following screenshot: 

Note Direction and Argument type.

The difference between a variable and an argument is that variables pass data to other activities, while arguments pass data to other workflows. The Arguments tab is next to the Variables tab at the bottom of the Studio screen.

4. We will always use a Try-Catch block to handle any exceptions gracefully. So, let's add the Try Catch activity to this sequence. Then, within the Try block, add the Read Range activity under Workbook to read the specified Excel file. The Read Range activity reads the value of a specified Excel range and stores it in a DataTable variable.

5. For the WorkbookPath, specify the RequestFilePath argument that we added in step 2. This argument should be populated with the path to Requests.xlsx when we invoke this workflow from Main. Your sequence and properties should look like this:

In the properties for the Read Range activities, perform the following steps:

• • Remove the range to cover the entire sheet. To do that, add “” to the Range property on the right pane.
  • While there, uncheck the AddHeaders property as we don’t have header in our input file.
  • Add an Output variable to store the data table. Use Ctrl + K to add the dtRequest variable within the DataTable property.

6. Next, we will use three Assign activities in the workflow to read from Excel and store the data for ContactName, Email, and Subject in respective arguments.
7. Use the arguments we just created on the left-hand side of the activity. You can start typing the argument names and the argument names should pop up for you to select:

8. We will use the dtRequest.Rows(row)(column) data table to read Excel values and map them to the arguments. For example, dtRequest.Rows(0)(1) means the first row and second column value in Excel. Since the output variable only accepts the values of the String type, we have to add .tostring at the end of this formula; for example, dtRequest.Rows(0)(1).ToString:

9. After the Assign activities, add a Log Message activity to update the Excel read options in the system logs. This will help us to debug the workflow if needed:

That completes our Try block sequence. We will now add exception handling to the Catches block.