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 :
- 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.
cn.Open(strConn);
- 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 recordsetobject.name.To 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.
index++;
}
- 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
ctx.log(rs("F1").value,e.logIconType.Info);
ctx.log(rs("F4").value,e.logIconType.Info);
rs.MoveNext();
index++;
}
- 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 = sc.data;
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'";
cn.Open(strConn);
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() ;
rootData.PODetails[index].Supplier=rs("Supplier").value;
rootData.PODetails[index].Quantity=rs("Quantity").value;
ctx.log(rs("Supplier").value,e.logIconType.Info);
ctx.log(rs("Quantity").value,e.logIconType.Info);
rs.MoveNext();
index++;
}
rs.Close();
cn.Close();
sc.endScenario();
sc.endStep(); // end Scenario
return;
}});
Conclusion
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.
