cancel
Showing results for 
Search instead for 
Did you mean: 

Verifying data in Excel file

SOLVED
Highlighted
Contributor

Verifying data in Excel file

My tested application outputs the reports as excel files. These files have user and date information which does not lend them well to a file comparison. I'd like to be able to verify data contained in a range of rows and columns contained in selected sheets of the excel file.

 

This topic has been broached previously, and I believe Robert was on the right track in this post, however, it does not have enough detail for me. https://community.smartbear.com/t5/TestComplete-General-Discussions/How-to-test-the-content-of-an-Ex...

 

I am able to use the following script routine to get the data from select sheets of my outputted excel file. Can I now use this information that is contained in the Test Log to compare to baseline values? How is that done? How can I specify the rows and columns? 

 

var RecNo;
  
// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;
  
  Fldr = Log.CreateFolder("Record: " + aqConvert.VarToStr(RecNo));
  Log.PushLogFolder(Fldr);
  
  for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++)
    Log.Message(DDT.CurrentDriver.ColumnName(i) + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value(i)));
  
  Log.PopLogFolder();
  RecNo = RecNo + 1;
}
  
// Creates the driver (main routine)
function TestDriver()
{
  var Driver;
  
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  Driver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");
  
  // Iterates through records
  RecNo = 0;
  while (! Driver.EOF() )
  {
    ProcessData(); // Processes data
    Driver.Next(); // Goes to the next record
  }
  
  // Closes the driver
  DDT.CloseDriver(Driver.Name);
}

 

It seems that this is where the data driven loop comes in, but I don't know how to make use of that to confirm values. 

 

Apologies if this is a duplicate post in some way. I tried to post this question previously but was not able to find record of it, so I am posting again.

 

Thanks,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: Verifying data in Excel file

Gotcha... OK, then... rewriting your code a bit.  Note that I'm removing the ProcessData function as all that is doing is going through the driver an writnig to the log.  

 

function TestDriver()
{
  var Driver;
  
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  TestDriver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");
  BaseDriver = DDT.ExcelDriver("C:\\BaselineFile.xls", "Sheet1"):

  
  // Iterates through records
  RecNo = 0;
  while (!TestDriver.EOF() && !BaseDriver.EOF())
  {
    for (var i = 0, i < TestDriver.ColumnCount, i++) {
       if (TestDriver.Value(i) <> BaseDriver.Value(i)) {
       Log.Warning("Test file column " + TestDriver.ColumnName(i) + " does not match baseline")
       }
   }
    TestDriver.Next(); // Goes to the next record
    BaseDriver.Next();
  }
  
  // Closes the driver
  DDT.CloseDriver(TestDriver.Name);
  DDT.CloseDriver(BaseDriver.Name);
}

In side the for loop is where you do all your comparisons, etc.  I only included a very basic log warning but you could detail it out more with the values of test versus the values of baseline, etc.  Generally, this is what you're looking for... looping through BOTH files in parallel, one row at a time, comparing column values.


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

View solution in original post

13 REPLIES 13
Highlighted
Community Hero

Re: Verifying data in Excel file

Rather than outputting to the log, you could run while loops on both the DDT drivers.  Something like

 

while (!driver1.EOF) and (!driver2.EOF) {
if driver1.column1.value <> driver2.column1.value
    Log.Message('The values don't match")
else Log.Message("the values match")
driver1.Next
driver2.Next }

This is just pseudo code, mind, as it seems you have a good handle on how to set up the drivers, etc.  But this is how I would do the row by row, column by column comparison of the two EXCEL files.


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
Contributor

Re: Verifying data in Excel file

Thank you Robert. I don't really have a handle on the code side. I stick to KeyWord Tests. However needing to verify data contained in only sections of an excel file is pushing me to needing more capability. The section of script I literally took from the following link and used it:

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/drivers.html

Highlighted
Community Leader

Re: Verifying data in Excel file

Row and Column start from 0

Each time you use the Driver.Next the row increment by one

On each row you acces the column i with DDT.CurrentDriver.Value(i)  (remember index start to 0).

 

The DDT method work without licence of Excel, just need the MSSoft access driver.

 

If you have Excel licence you can use another approach and access directly to the desired cell.

I made an example for you:

 

function readExcel(Ligne, Colonne) {
  let excelObject, excelWorkbookObj, excelMainSheet, sheetName;
  let result = "";
  try {
    sheetName                 = "your sheet name";
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open("your path and name of excel file");
    excelMainSheet            = excelWorkbookObj.Sheets.item(sheetName);
    // Don't use .Text because it's displayed value nor .Value because it can be affected by cell formatn .Value2 is the underlying value, so the only choice.
    result = excelMainSheet.Cells.Item(Ligne, Colonne).Value2; 
  }
  catch(e) {
    Log.Error("Erreur durant la lecture Excel", e.message);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
      // Use your own method to kill process to avoid remaining EXCEL process in memory
      system.killProcess("EXCEL");
    }
  }
return result;
}

 

Un sourire et ça repart

Highlighted
Community Hero

Re: Verifying data in Excel file

Gotcha... OK, then... rewriting your code a bit.  Note that I'm removing the ProcessData function as all that is doing is going through the driver an writnig to the log.  

 

function TestDriver()
{
  var Driver;
  
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  TestDriver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");
  BaseDriver = DDT.ExcelDriver("C:\\BaselineFile.xls", "Sheet1"):

  
  // Iterates through records
  RecNo = 0;
  while (!TestDriver.EOF() && !BaseDriver.EOF())
  {
    for (var i = 0, i < TestDriver.ColumnCount, i++) {
       if (TestDriver.Value(i) <> BaseDriver.Value(i)) {
       Log.Warning("Test file column " + TestDriver.ColumnName(i) + " does not match baseline")
       }
   }
    TestDriver.Next(); // Goes to the next record
    BaseDriver.Next();
  }
  
  // Closes the driver
  DDT.CloseDriver(TestDriver.Name);
  DDT.CloseDriver(BaseDriver.Name);
}

In side the for loop is where you do all your comparisons, etc.  I only included a very basic log warning but you could detail it out more with the values of test versus the values of baseline, etc.  Generally, this is what you're looking for... looping through BOTH files in parallel, one row at a time, comparing column values.


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

View solution in original post

Highlighted
Contributor

Re: Verifying data in Excel file

Thanks Robert, I am amazed that this is so few lines. I do get "Unexpected token <" on line 17 "for (var i = 0, i < TestDriver.ColumnCount, i++) {"

 

Why is that? If I change that to an "=" sign then I get "Unexpected token ++" on the same line.

Highlighted
Community Leader

Re: Verifying data in Excel file

Equality is done by ==,  single = is assignation.

Un sourire et ça repart

Highlighted
Community Hero

Re: Verifying data in Excel file

Ah... replace the "," with ";" in the for statement.  My bad... rapid typing without thinking.


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
Community Leader

Re: Verifying data in Excel file

... and my bad .. bad reading  ...

 Smiley Frustrated

Un sourire et ça repart

Highlighted
Community Hero

Re: Verifying data in Excel file

Corrected for loop:

 

   for (var i = 0; i < TestDriver.ColumnCount; i++) {
       if (TestDriver.Value(i) <> BaseDriver.Value(i)) {
       Log.Warning("Test file column " + TestDriver.ColumnName(i) + " does not match baseline")
       }

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
New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!

Challenge Status

How to execute remote test and obtain results via Test Runner REST API

Participate!

Comparing content of HTML table with Excel file data

Participate!

Compare HTML table with Excel file and correct data in the file

Participate!

Retrieve Data from a Web Page

Participate!
Top Kudoed Authors