Forum Discussion

maumrm's avatar
maumrm
Contributor
5 years ago

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 i...
  • tristaanogre's avatar
    tristaanogre
    5 years ago

    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.

tristaanogre's avatar
tristaanogre
Esteemed Contributor

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.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    5 years ago

    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.

    • maumrm's avatar
      maumrm
      Contributor
      5 years ago

      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.

      • BenoitB's avatar
        BenoitB
        Community Hero
        5 years ago

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