Validating Data in Excel Files

Applies to TestComplete 14.70, last modified on April 22, 2021

To validate data in Excel files, use the Excel checkpoint.

In keyword tests, you perform Excel checkpoints with the Excel Checkpoint operation:

Excel Checkpoint in a keyword test

In scripts — by using the Files.Excel_File_Name.CheckExcelWorkbook method:

JavaScript, JScript

Files.DataStorageExcel1.CheckExcelWorkbook("C:\\Temp\\DataStorageExcel.xlsx");

Python

Files.DataStorageExcel1.CheckExcelWorkbook("C:\\Temp\\DataStorageExcel.xlsx")

VBScript

Call Files.DataStorageExcel1.CheckExcelWorkbook("C:\Temp\DataStorageExcel.xlsx")

DelphiScript

Files.DataStorageExcel1.CheckExcelWorkbook('C:\Temp\DataStorageExcel.xlsx');

C++Script, C#Script

Files["DataStorageExcel1"]["CheckExcelWorkbook"]("C:\\Temp\\DataStorageExcel.xlsx");

Follow the steps below to use the Excel checkpoint in your tests:

1. Launch the Wizard

You can create Excel checkpoints both during test recording and when editing the test. The way you add the checkpoint to the test depends on the test creation mode:

  • Test recording –

    1. Click Add Check on the Recording toolbar.

    2. In the Checkpoint Wizard, select File, and then click Next.

      Excel Checkpoint: Selecting checkpoint type

      Click the image to enlarge it.

  • Editing a keyword test –

    1. Select the Checkpoints category in the Operations panel of the Keyword Test editor.

    2. Find the Checkpoint Wizard operation in the list and drag it to your test. In the Checkpoint wizard, click File.

      – or –

      Find the Excel Checkpoint operation in the list and drag it to your test.

      Excel Checkpoint: Adding Excel checkpoints to keyword tests
  • Editing a script test –

    1. Open the script to be edited.

    2. Click on the Code Editor toolbar:

      Adding checkpoints to script tests
    3. In the Checkpoint Wizard, select File, and then click Next.

2. Specify the Excel File to Check

On the next page of the wizard, you specify the type of the file you want to verify. Click Excel to verify an Excel file (if you are configuring the Excel Checkpoint operation, the wizard will automatically select Excel comparison).

Enter the fully qualified name of your Excel file in the Specify an Excel file to check edit box or click the ellipsis button and select the file via the standard Open File dialog.

In the Options block, specify whether TestComplete will compare the entire Excel file or a specific sheet. Use the The first row is a header check box to command TestComplete to recognize the first row of the selected sheet as the table header.

Excel Checkpoint: Specifying a Excel file to check

Click the image to enlarge it.

To proceed with the checkpoint creation, click Next.

TestComplete will display the next page of the wizard that depends on your chosen comparison object (an entire file or a specific sheet).

3. Specify the Expected File and the Data to Check

Validating an entire file

On this page, you can select a source of the baseline text content that will be used for verification:

Excel Checkpoint: Specifying baseline data

Click the image to enlarge it.

  • To use the selected file as the baseline one, click Current file contents.

    In the Save the file to Stores as a baseline copy text box, enter the name of the Stores > Files collection element that will store the baseline copy.

    Note: This name will be used to address the element from tests, so it must be a valid identifier. To create a name that will match the rules of any scripting language supported by TestComplete, start it with a letter and specify only letters, digits, and underscore symbols.
  • To select an existing Stores > Files item that contains the baseline text, click File from Stores. Select the needed item from the drop-down list.

Validating a sheet

On this page of the wizard, you can specify what rows and columns of the sheet will be stored and verified.

Excel Checkpoints: The Select Data to Validate page of the Excel Checkpoint wizard

Click the image to enlarge it.

  1. By default, the checkpoint will verify all rows, which the wizard retrieves from the table.

    To limit the number of rows that the checkpoint will verify, select the Validate specific number of rows check box, and then enter the desired number of rows.

    To verify all table rows, clear the check box.

  2. Select the columns that the checkpoint will verify:

    • To mark a table column for storing and verification, select its check box in the Store column of the page.

    • To mark a table column as a Key column, select its check box in the Key column of the page. TestComplete uses the key column (or a combination of several columns) to identify rows. To provide a unique identification of table records, key column values must be different in each row. The key columns affects how the table checkpoint performs the verification (see How the Table Comparison Procedure Works).

In the The name of the Stores object text box, enter the name for your baseline data. This name will be used in tests to address the checkpoint.

On the Preview tab page of the wizard, you can view the data that the wizard has retrieved from the table according to the specified criteria.

Excel Checkpoints: Preview the table data

Click the image to enlarge it.

Here you can make sure that the Excel checkpoint will verify the desired data.

4. Close the Wizard

Click Finish to close the Checkpoint wizard. TestComplete will add the checkpoint statement for verifying the specified Excel file to your test.

In keyword tests

The added Excel Ceckpoint in keyword tests

Click the image to enlarge it.

In script tests

If you are creating an Excel checkpoint while editing a test script, then the generated checkpoint code will be displayed in the Copy Text to Clipboard dialog. You will be able to copy this code to the clipboard and then paste it to the desired place in your script:

The added Excel Ceckpoint in script tests

Click the image to enlarge it.

5. Run the Test

To run the test and validate the specified Excel data, you can do the following:

In keyword tests

Click Run Test on the Keyword Test editor's toolbar.

Running keyword tests

Click the image to enlarge it.

In script tests

Place an insertion point inside the routine code and then click Run This Routine on the Code Editor's toolbar.

Running script tests

Click the image to enlarge it.

For complete information about all available ways to run your tests, see Running Automated Tests.

6. Analyze Results

When your test is executed, TestComplete will show you comparison results of the Excel checkpoint in the Test Log.

If a checkpoint passes, TestComplete posts a "successful checkpoint" message ( ) to the test log.

If a checkpoint fails, it posts an error message ( ) and logs the verification summary to the Details panel of the test log:

Details panel containing Excel checkpoint results

Click the image to enlarge it.

In addition, you can update the Excel checkpoint's baseline data from the test log. To do this, click the Update the <Checkpoint_Name> checkpoint data link at the bottom of the Details panel.

See Also

Working with Microsoft Excel Files
Reading Data in Excel Files
Writing Data in Excel Files

Video tutorial
 
Highlight search results