How get the Specific Row value from an Excel Sheet with the Help of DDT Methods

[abdul_rp]

Hi,



I am using following code to fetch the Values from Excel Sheet , I am Incrementing Driver to fetch the value of every Row in the Excel Sheet.



If I want the value of 10th Row in 1st Column, How can I get it ? , For that do I require to Increment the Driver 10 times? Kindly Advice .

Is there any alternate way to fetch the value?

 

var invalid = DDT["ExcelDriver"]("E:\\OEC Trader\\DataFiles\\DataTables.xls", "InvalidLogin");

invalid = ["ExcelDriver"]("E:\\OEC Trader\\DataFiles\\DataTables.xls", "InvalidLogin");

trader["MainForm"]["StripMainMenu"]["Click"]("File|Logon");

while(!invalid["EOF"]() )

{

txtUserName["Text"] = invalid["Value"](0);

txtPassword["Text"] = invalid["Value"](1);

btnLogon["ClickButton"]();

if (btnOK["Exists"] == true)

{

btnOK["ClickButton"]();

invalid["Next"]();

Log["Message"](" Passed");

}

else

Log["Message"]("Failed");

break;

}

}



Regards,

Abdul

[tristaanogre]

DDT Driver objects iterate row by row so there's no easy way, given a particular driver object, to go to a specific row and find a value without looping through all the rows.



The alternative is to pass through to the driver object a command text that is, in essence, an SQL query that limits the records returned... however, doing so would alter the record set in the driver object.



The BEST way to get a particular value from a particular row and column in an excel doc is to use the Excel application COM object.  You can probably adapt the code found at http://smartbear.com/support/viewarticle/20878/ to, instead of writing to a cell, getting the value of that cell.

[irina_lukina]

Hi, Abdul.

1.

If I want the value of 10th Row in 1st Column, How can I get it ? , For that do I require to Increment the Driver 10 times?

If you need to get this value using the DDT Drivers, I'm afraid, incrementing the current value several times is the only way. However, I've registered your suggestion to provide access to a specific cell through DDT drivers in our DB. Thanks for your feedback.

2.

Is there any alternate way to fetch the value?

Yes, you can access the needed value by its index when working with Excel via COM. For this purpose, you can use, for example, the following code:

function ReadDataFromExcel()

{

  var Excel = Sys.OleObject("Excel.Application");

  Excel.Workbooks.Open("C:\\MyFile.xls");

  var value=Excel.Cells(10,1);

  Log.Message("The value is: " + VarToString(value));

  Excel.Quit();

} 

For more information on working with Excel via COM, see the Working With Excel Files via COM help topic.

[abdul_rp]

Thanks Robert and Irina for providing the solution.

[chuynh]

Hi, I am curious if Abdul's #1 question has been implemented yet.  I am looking for such a feature.  Please advice.

Thanks.

[murugans1011]

I am also looking for such feature..

[TanyaYatskovska]

Hi Charles and Murugan,

This suggestion hasn't been implemented yet - its rating isn't high enough. I've added your votes to the suggestion.

At the moment, the only way to access the target cell by indexes is to use the approach suggested by Irina earlier.

[Ruhul_Ikram]

Hi,

I have similar question.

In Keyword Test, when I do data driven and covert to script I can mention which row to run or from specific row specific row, see below example:

function End_to_End_Referral_Specialist()
{
  //Launches the specified browser and opens the specified URL in it.
  Browsers.Item(btFirefox).Run(Project.Variables.EndtoEnd_Complaint3.Value("URL"));
  //Maximizes the 'BrowserWindow2' window.
  Aliases.browser.BrowserWindow2.Maximize();
  Project.Variables.EndtoEnd_Complaint3.Reset();

  var RecordIdx;
  for(RecordIdx = 1; RecordIdx <= 16; RecordIdx++)
  {
       Log.AppendFolder(Project.Variables.EndtoEnd_Complaint3.Value("Test Name"), Project.Variables.EndtoEnd_Complaint.Value("Description"));
    //Sets the text KeywordTests.End_to_End_Referral_Specialist.Variables.EndtoEnd_Complaint("Username") in the 'textboxUsername' text editor.
    Aliases.browser.formLoginform.textboxUsername.SetText(Project.Variables.EndtoEnd_Complaint.Value("Username"));
    //Sets the text KeywordTests.End_to_End_Referral_Specialist.Variables.EndtoEnd_Complaint("Password") in the 'passwordboxPassword' text editor.
    Aliases.browser.formLoginform.passwordboxPassword.SetText(Project.Variables.EndtoEnd_Complaint.Value("Password"));
    //Clicks the 'submitbuttonLogin' control.
    Aliases.browser.formLoginform.submitbuttonLogin.ClickButton();

    Aliases.browser.linkLogout.Click();
    Log.PopLogFolder();
    Project.Variables.EndtoEnd_Complaint3.Next();
  }

but when I try to do it custom functions it only loops through 1st row 4 times, see below codes:

function DataSheet (tab){
 // Creates a driver
 DDT.ExcelDriver("C:\\AutomationFiles\\test.xls", tab, true);
 // Iterates through records
 ProjectSuite.Variables.TestName = DDT.CurrentDriver.Value("Test Name");
 ProjectSuite.Variables.TestDescription = DDT.CurrentDriver.Value("Description");
 ProjectSuite.Variables.Username = DDT.CurrentDriver.Value("Username");
 //Log.Message(ProjectSuite.Variables.Username)
 ProjectSuite.Variables.Password = DDT.CurrentDriver.Value("Password");
 //Log.Message(ProjectSuite.Variables.Password)
 DDT.CurrentDriver.Next();
 // Close the driver
 DDT.CloseDriver(DDT.CurrentDriver.Name);
}


function excelSmokeTest() {
//check Cust_Variables to see what browser and env we're testing, open that browser
CheckBrowser(ProjectSuite.Variables.URL);
  var RecordIdx;
  for(RecordIdx = 1; RecordIdx <= 4; RecordIdx++) 
  {  
    DataSheet('E-T-E')
    Log.AppendFolder(ProjectSuite.Variables.TestName, ProjectSuite.Variables.TestDescription)
    //input username/pw to login
    login(ProjectSuite.Variables.Username,ProjectSuite.Variables.Password);
    MAXe_logout();
    Log.PopLogFolder();
    }
 }

Not sure what am I missing and if I run below code it fails:

function DataSheet (tab){
  // Creates a driver
  DDT.ExcelDriver("C:\\AutomationFiles\\test.xls", tab, true);
  // Iterates through records
  while (! DDT.CurrentDriver.EOF()){
  ProjectSuite.Variables.TestName = DDT.CurrentDriver.Value("Test Name");
  ProjectSuite.Variables.TestDescription = DDT.CurrentDriver.Value("Description");
  ProjectSuite.Variables.Username = DDT.CurrentDriver.Value("Username");
  //Log.Message(ProjectSuite.Variables.Username)
  ProjectSuite.Variables.Password = DDT.CurrentDriver.Value("Password");
  //Log.Message(ProjectSuite.Variables.Password)
   DDT.CurrentDriver.Next();
     }
  // Close the driver
  DDT.CloseDriver(DDT.CurrentDriver.Name);
}

function excelSmokeTest() {
//check Cust_Variables to see what browser and env we're testing, open that browser
CheckBrowser(ProjectSuite.Variables.URL);
  var RecordIdx;
  for(RecordIdx = 1; RecordIdx <= 4; RecordIdx++) 
  {  
    DataSheet('E-T-E')
    Log.AppendFolder(ProjectSuite.Variables.TestName, ProjectSuite.Variables.TestDescription)
    //input username/pw to login
    login(ProjectSuite.Variables.Username,ProjectSuite.Variables.Password);
    MAXe_logout();
    Log.PopLogFolder();
    }
 }

[twackerla]

Just checking in to see if this had been implemented. If not, would you add me to the list of people who would like this?

Thank you,

Tyson