Skip to Content
Technical Articles
Author's profile photo Nabheet Madan

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!.

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vijay Sharma
      Vijay Sharma

      Nice bhai!! I tried the SAP example of web search writing to excel ... I agree, sky is the limit… ?

      Author's profile photo Luis Guerra
      Luis Guerra

      Hi Vijay

      I tried to do Web Search Excel Output project but I didn't have success.

      Could you help me with it. I've attached my project in google drive.

      https://drive.google.com/open?id=1P-fZCsxPZ-TVarrxMzVmjcLDqsEJJC5W

      I really thank your help or whatever hints you could give me.

      Author's profile photo Ashish Santosh Kale
      Ashish Santosh Kale

      Thanks Nabheet, very insightful!

      Author's profile photo Ahmed Ali Khan
      Ahmed Ali Khan

      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.

      Author's profile photo Nabheet Madan
      Nabheet Madan
      Blog Post Author

      thanks Ahmed khan for the feedback. It is in my todo list, unless someone else post about it:)

      Author's profile photo M Depa
      M Depa

      Great Nabeeth... Could you please also help with inputs on reading data from PDF file and uploading in SAP system..

      Author's profile photo Nabheet Madan
      Nabheet Madan
      Blog Post Author

      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

      Author's profile photo Martin Eberle
      Martin Eberle

      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

      Author's profile photo Jan Pascal Tschudy
      Jan Pascal Tschudy

      Hey Martin

      After you've identified the workbook from all the opened excel instance that you want to work with, you have to call the function get_workbook and pass the name (list[n]) to carry on.

      Greetings

      Author's profile photo Esteban Orrego
      Esteban Orrego

      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

      Author's profile photo M Depa
      M Depa

      Hi Nabeeth,

       

      Thanks for the blog, it is very use full. I am following all these blogs as I am new to this tool.

      Could you please advice me which approach is best for reading data from excel file and uploading to SAP transaction.  My requirement is to update VA11 transaction..

       

      Thanks,