The amazing world of IBM ODM and more

The amazing world of IBM ODM blog - Angello Manrique Vigil
  • HOME
  • ABOUT ME
  • CONTACT ME

RPA – UiPath: Working with Excel

Sunday, 26 August 2018 by Angello Manrique Vigil

In this blog post we will talk about working with Excel files in one of the top RPA tools: UiPath. As we know Robotic process automation (RPA) is the practice of automating routine business practices with “software robots” that perform tasks automatically. These tasks include transaction processing, IT management and automated online assistants. These software robots could replace human beings for common tasks, being Excel one the most used business tools in the world.

To understand the basics of UiPath, please refer to this link.


In this example we are going to use a case defined in a practice exercise of the UiPath academy where we have different values in 2 columns (A and B) and we need to write the sum in the column C. This is a pretty simple task but we are going to do it in 3 different ways and analyze the development and execution time for each solution.

The input file example:

RPA UiPath Excel sample

Output file example:

RPA UiPath Excel sample

First method: Using Excel Application Scope and Get Row activities

  1. This is standard way. We are going to have the following composition:
    • Main Sequence
      • Excel Application Scope
        • Read Range
        • For each row
          • Find current row
          • Get Row A
          • Get Row B
          • Assign sum to value C
          • Write value C in C ColumnRPA UiPath Excel First Method Outline
  2. Let me explain the steps of this method:
    1. We open the Input excel file with the Excel Application Scope
    2. We read the values using the “Read Range” activity and saving the output in a Datatable variable
    3. Now for each row we get the Column A and B values with the “Get Row” activity
    4. We add the total to third variable
    5. We use the “Write Cell” activity to write the third value in the Column C

Second method: Without using Excel Application Scope

  1. In this method we will do everything internally and we are going to create the C column manually. This is the composition:
    • Main Sequence
      • Read Range
      • Add data column
      • For each row
        • Assign A value
        • Assign B value
        • Set the value for Column C
        • Write RangeRPA UiPath Excel Second Method Outline
  2. Explanation:
    1. We read the values using the “Read Range” activity and saving the output in a Datatable variable
    2. We add the Column C using “Add Data Column” activity
    3. Now for each row we get the Column A and B values using “Assign” activity
    4. We assign the sum to the Column C (row (2)) value parsing to the A and B values to integer
    5.  We write the whole table back to the original excel or a new file using “Write Range” activity

Third method: Using Excel Application Scope and Excel formulas

  1. In this method we will use the Excel internal formulas to sum the values and save coding time. This is the composition:
    • Main Sequence
      • Excel Application Scope
        • Read Range
        • Assign rows count
        • Write ValueRPA UiPath Excel Third Method Outline
  2. Explanation:
    1. We read the values using the “Read Range” activity and saving the output in a Datatable variable
    2. We find out the number of rows and assign it to a variable
    3. Now we finish by writing directly “=SUM(A1,B1)” in the “C1:C”+rowsCount range.

Conclusion:

We have successfully automate the use case using 3 different methods. Now let’s analyze the results and benefits of each case using 3 aspects:

  • Development: involving the difficulty of doing it and how robust and easy to understand the code is.
  • Execution time: how fast the robot executes the code
  • Benefits for the business

RPA UiPath Excel samples conclusion

In conclusion, the 3 methods work ok and it will depend on the business needs and developers to use the correct way for each process.

I hope you enjoy this article about UiPath and as always feel free to contact me with any questions or feedback. Thank you!

ListsRPAUiPath
Read more
  • Published in RPA, UiPath
No Comments

Working with Lists in the XOM / BOM

Wednesday, 24 May 2017 by Angello Manrique Vigil

The XOM (Execution Object Model) and BOM (Business Object Model) are key artifacts of our Rule project. The execution object model (XOM) is the model against which you run rules, it references the application objects and data, and is the base implementation of the business object model (BOM). For more information about XOM/BOM visit the IBM Knowledge Center here.

We can generate our BOM entries from a XOM (Java or XSD) and get the objects we will verbalize for our rules. A very common requirement in a project is to work with Lists and return a populated List with values from a Rule Action or Decision Table. You will notice that after you create your BOM entry from an XOM with Lists (maxOccurs=”unbounded”) you will get an object of type java.util.Vector but with no direct relationship between the elements. Please review the following problem example and a workaround on how to fix it and implement Lists correctly.


Problem:

Let’s say you have an XSD with an element like this:

<complexType name=”RecruitsList”>
         <sequence>
                  <element name=”recruits” type=”tns:Recruit” maxOccurs=”unbounded” minOccurs=”0″></element>
         </sequence>
</complexType>

List XSD Samples XOM

After you create the BOM entry, you will get an object like this:

BOM Lists ODM sample

With no proper verbalization and without a way to use it in a Rule Action, Decision Table or Decision Tree correctly. In this example we want to add objects of type Recruit to our RecruitsList and display this list in the output of our Decision Service. Follow the steps below to implement a workaround and accomplish our goal.


Steps:

  1. Creating a new Method member
    • Let’s remove this verbalization and navigate to the Class tab.
    • We are going to create a new method to add our Objects. Click “New…” in the Members section.
    • Select “Method”, give a proper Name and “void” for Type.
    • Now add Arguments according to the Object you are going to add to your list, in our example the Recruit’s attributes. At the end you should have something like this:BOM new method member sample
    • Tip: Remember that the types have to match, if you are using a virtual value assign the corresponding type like the example. For more information about dynamic domains and virtual values read my previous post here.
    • Now we need to create a proper verbalization for our new method and implement the BOM to XOM Mapping logic.
    • Navigate to the Member Verbalization section and click “Create”. Modify the Action phrase according to your Object. In this sample:BOM method verbalization sample
    • The last and most important step is to define our BOM to XOM Mapping logic. We need to map our method arguments with the attributes of our Object, in this sample with the Recruit’s age, country, name and sex:BOM to XOM mapping logic sampleTip: Don’t forget to initialize the response list variable in your ruleflow or variableSet.

Results:

Now we are ready to use our method in any Rule and return a list structure schema in our Decision Service response!

Decision Table demonstration sample:

Decision Table method sample

After we test our rules we will see this list structure in the response (Testing with SoapUI):

BOM XOM Rule Test List SoapUI

Please feel free to comment or contact me with any doubts or feedback. Thank you!

BOMBOM2XOMJavaListsRuleDesignerSoapUIXOM
Read more
  • Published in Java, Rule Authoring, Rule Designer, Rule Execution Server (RES)
No Comments

Recent Posts

  • RPA – UiPath: Working with Excel

    In this blog post we will talk about working wi...
  • ODM on Docker!

    If you are a DevOps enthusiast like me you prob...
  • RPA: UiPath and ODM integration

    In this technical tutorial we will have a look ...
  • Populating Decision Tables with external data sources

    One of the most used artifacts in IBM ODM is th...
  • Decision Composer, integration with Rule Designer

    In this post I will show a new way to start you...
  • Connecting to external Database with ODM API

    The IBM ODM Decision Center and the Rule Execut...
  • Custom log file with ODM API

    Given the positive feedback from previous post ...
  • Working with the Decision Center API

    In many IBM ODM rules implementation projects w...
  • Automating deployments with Ant tasks

    To deploy RuleApps to Rule Execution Server, au...
  • Editing business rules in Microsoft Office

    IBM ODM Decision Center Business Console provid...

Categories

  • Ant Tasks
  • Bluemix
  • Cloud
  • Database
  • Debug
  • Decision Center
  • Decision Operation
  • Decision Table
  • Decision Warehouse
  • Docker
  • Domains
  • Java
  • ODM API
  • RPA
  • Rule Authoring
  • Rule Designer
  • Rule Execution Server (RES)
  • Rule Solutions for Office
  • Ruleflow
  • UiPath

Recent Posts

  • RPA – UiPath: Working with Excel

    In this blog post we will talk about working wi...
  • ODM on Docker!

    If you are a DevOps enthusiast like me you prob...
  • RPA: UiPath and ODM integration

    In this technical tutorial we will have a look ...
  • Populating Decision Tables with external data sources

    One of the most used artifacts in IBM ODM is th...
  • Decision Composer, integration with Rule Designer

    In this post I will show a new way to start you...

Recent Comments

  • Mastan on Debugging a simple Ruleflow in Rule Designer
  • Angello Manrique Vigil on ODM on Docker!
  • brian on ODM on Docker!
  • Angello Manrique Vigil on Populating Decision Tables with external data sources
  • Pramod on Populating Decision Tables with external data sources

Subscribe

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

All rights reserved. Made by Angello Manrique Vigil.

TOP