Technical Articles
My trials with SAP Intelligent RPA – Excel Reading data via inbuilt activity, context concept & scripting
Posts in the series
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!.
Nice bhai!! I tried the SAP example of web search writing to excel ... I agree, sky is the limit…
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.
Thanks Nabheet, very insightful!
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.
thanks Ahmed khan for the feedback. It is in my todo list, unless someone else post about it:)
Great Nabeeth... Could you please also help with inputs on reading data from PDF file and uploading in SAP system..
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
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
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
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,