Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
Showing results for 
Search instead for 
Did you mean: 
Reading worksheet data, mostly from excel worksheet is quite a common use-case in RPA. Querying worksheets as a database gives the flexibility to select only the necessary columns, overcome unnecessary null values and filtering data during reading.

Core Benefits :

  • Selecting the necessary columns irrespective of the position.

  • Join Multiple worksheets data easily.

  • No impact in the Bot if the start cell gets change from worksheet to worksheet.

  • No impact in the Bot If Null/Blank row is present in-between data.

  • Essay to apply filter during worksheet reading.

  • One can easily sort the data during the query.

  • Faster if a huge amount of data needs to read and if multiple worksheets are involved.

Pre-requisite :

Microsoft Access Database installed.

Steps to Read Data Using OLEDB connection :

  • Create an object of ADODB Connection and Recordset.
     var cn = new ActiveXObject("ADODB.Connection");​ // object for connection
    var rs = new ActiveXObject("ADODB.Recordset"); // object for record set

  • Provide the provider and workbook path. If you add HDR=YES as extended properties then the first row of the data set will be considered as the header of the column else column name will come as F1, F2 and so on.
    var strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = 'C:\\BotDevelopment\\Sample\\local\\POs.xlsx';Extended Properties='Excel 12.0;HDR=Yes'";​

  • Open the connection. This will create a connection with the workbook.

  • Set the query which you want to execute. Please make sure you have added "$" at the end of the worksheet name.
    //In the below queries "Supplier" and "Quantity" is the name of the column in excel workbook and "Sheet1" is the name of the worksheet.

    var SQL = "select * from [Sheet1$] where [Supplier] is not NULL";​ //Only to read rows where Supplier Value is provided

    var SQL = "select [Supplier],[Quantity] from [Sheet1$]"; // To select data for Supplier and Quantity column

  • Run the Query and data will be stored in the recordset Object.
     rs.Open(SQL, cn);​

  • Once Data is retrieved from the worksheet then one can loop the recordset. Values will be stored in the recordsetobject.value and column name will be stored in get the value from a recordset you can use the column name as an index.
    var index=0; // index variable to loop a array
    while(rs.EOF != true){ // till record is present in the recordset
    rootData.PODetails[index] = ctx.dataManagers.rootData_PODetails.create() ; //rootData.PODetails is an array defined in SAP Intelligent RPA Context
    rootData.PODetails[index].Supplier=rs("Supplier").value; //rs("Supplier").value is the Supplier value in the current record
    rootData.PODetails[index].Quantity=rs("Quantity").value; //rs("Quantity").value is the Quantity value in the current record

    ctx.log(rs("Supplier").value,e.logIconType.Info); // to print Supplier value in log
    ctx.log(rs("Quantity").value,e.logIconType.Info); // to print Quantity vaue in log
    rs.MoveNext(); // move to nect record in the current record set.

  • If you have not added the HDR= Yes then you can read the data using F1, F2.. as a column name.
    	var index=0;
    while(rs.EOF != true){
    rootData.PODetails[index] = ctx.dataManagers.rootData_PODetails.create() ;
    rootData.PODetails[index].Supplier=rs("F1").value; //rs("F1") denotes the first column of the recorset
    rootData.PODetails[index].Quantity=rs("F4").value;//rs("F4") denotes the fourth column of the recorset

  • Close the recordset and close the connection.
       rs.Close(); //close record set
    cn.Close(); // close connection​

Complete Code Snippet :

GLOBAL.step({ Custom: function(ev, sc, st) {
var rootData =;
ctx.workflow('ReadData', 'd845b031-08eb-489a-90dd-8bbd16f0f399') ;
// Custom
var cn = new ActiveXObject("ADODB.Connection");
var strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = 'C:\\RPAProject\\Sample\\local\\POs_Old.xlsx';Extended Properties='Excel 12.0;HDR=Yes'";
var rs = new ActiveXObject("ADODB.Recordset");
var SQL = "select * from [Sheet1$] where [Supplier] is not null";
rs.Open(SQL, cn);
var index=0;
while(rs.EOF != true){
rootData.PODetails[index] = ctx.dataManagers.rootData_PODetails.create() ;
sc.endStep(); // end Scenario


Now you have learned to use OLEDB connection to connect worksheets as a database. OLEDB connection can be used to connect other databases as well apart from excel or worksheet with the right provider and connection string.
1 Comment