Posts in the series
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.
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.
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.
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.
So with this approach We don’t need to specify an end row hopefully, so lets check the demo!
So in this demo you can clearly visualize how we are able to read cell by cell without specifying the last row etc.
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!.
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!.
- SAP Managed Tags:
11 Comments
