cancel
Showing results for 
Search instead for 
Did you mean: 

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

Contributor

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





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

8 REPLIES 8
Highlighted
Community Hero

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

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.

Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Super Contributor

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

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.

I’m not a member of the SmartBear Support Team, I’m just helping users in this community. Any views or opinions expressed do not necessarily represent those of SmartBear Software.
Highlighted
Contributor

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



Thanks Robert and Irina for providing the solution.
Highlighted
Not applicable

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

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

Thanks.
Highlighted
Regular Contributor

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

I am also looking for such feature..
Highlighted
Community Manager

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


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.


 

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Occasional Contributor

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

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();
    }
 }

 

Highlighted
Senior Member

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

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

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors