Skip to Content
Technical Articles

My trials with SAP Intelligent RPA – Excel Reading data via inbuilt activity, context concept & scripting

Posts in the series

Part Description
1 Hello Excel – Set Cell value and different formatting options
2 Hello Excel – Reading range of data via inbuilt activity, look at Concept of Context briefly and scripting
3 UI Designer design screen for data read from excel file

What we intend to do

In our first post we have seen how we were able to modify the value of a cell along with some formatting in excel file. But do we always need to just modify a cell value..? A big No we need to read the whole set of rows of data contained in worksheets etc. So lets take our understanding to the next level. We have an excel file with around 10 rows of data which we will try to read. We will look at two different approaches of reading the same data and the reason behind the same.

Approach 1- Using inbuilt activity

We have reused the steps till we open an excel file worksheet from our previous post.

So now instead of adding set cell value we want to read so add get range values.  We need to specify start column, end column, start row, end row and the context in which our data will hold.

You must be thinking what is this context? Context is nothing more like variable or containers in workflows which will hold data in a very basic form. In order to create a variable( can be a structure) all you need to do is  do a right click and provide the name. The checkbox next to it is in case you expect this variable to be an array.

So now we have our variable assigned and data read, lets use another activity type named log so that we can see the values in execution log. The log activity type is more like console.log or SLG1 we use in ABAP or Javascript.

Then we will close and end the excel.

Approach 1- Demo

As you can see below we are able to read the data and can verify it in the log, but I was not still convinced with the demo why…? Do we need actually specify the last row number? Most of the times I will never know it so that is what the search led me the discovery of scripting.

Approach 2- Using some scripting

As you are aware once we built the project we have Javascript file which is generated. I added a custom activity step before the log step so as to do some scripting.

So I thought since this is nothing but an array of values so some how if i can start a loop with an exit condition and call the method recursively ,we can read all the values.  Lets add logging of values also just to cross check if our logic is working.

var loopcount;
for (loopcount = 1; ctx.excel.sheet.getCell(loopcount, 'A'); loopcount++) {
  ctx.log(ctx.excel.sheet.getCell(loopcount, 'A'));
}

So with this approach We don’t need to specify an end row hopefully, so lets check the demo!

Approach 2- Demo

So in this demo you can clearly visualize how we are able to read cell by cell without specifying the last row etc.

What is next?

So now we have explored how to read data via different approaches, in next step we will explore some more hidden methods, a way to present this data if possible, mail this data, upload this data in SAP, error handling, debuggin etc.. I can think of infinite possibilities such as SAP Conversational AI triggering the RPA bots and what not sky is the limit i will say!.

10 Comments
You must be Logged on to comment or reply to a post.
  • /
    🙂
  • Hi Nabheet,

    This is an awesome blog , could you also create a blog to upload data from excel file into hana cloud trial account with step by step procedure, that is really needed.

    • Hello Jwala Deepa

      I think uploading is not a problem reading might be. But if you have fixed length of pdf fixed places UI automation can be used to extract the data. I am still on my exploring phase better post it in Q&A so that experts can help.

      Thanks

      Nabheet

  • Hi

    What is the approach to read data out of an opened EXCEL (the user did already open the xls file containing some data)?

    I tried with initialize like below, but it looks like it can’t attach to the opened EXCEL….

    // —————————————————————-
    // Step: Initialize_Excel
    // —————————————————————-
    GLOBAL.step({ Initialize_Excel: function(ev, sc, st) {
    var rootData = sc.data;
    ctx.workflow(‘XLSTest1’, ‘040d1db7-266b-4a53-91ce-3068455fcbd9’) ;
    // Initialize Excel
    ctx.options.excel.newXlsInstance = false;
    ctx.options.excel.visible = true;
    ctx.options.excel.displayAlerts = false;
    ctx.excel.initialize();
    var list = ctx.excel.getWorkbooks();
    var nbWB = ctx.excel.getWorkBooksLength();
    ctx.log(‘There is ‘+nbWB+’ workbooks associated to the current Excel instance.’);
    for (var i=1; i<=nbWB;i++){
    ctx.log(‘Workbook n°’+i+’ is named: ‘+list[i-1]);
    }

    sc.endStep(); // Get_workbook
    return;
    }});

     

    Regards

  • Hi,

    how can I dynamically get the data range, not like in your example which marks from A1 to D9. if not, as new records are entered, take them as well. And How can I send information from an excel to SAP ERP on premises, have u any example.

    Regards