Validating Data in Excel Files

1. Launch the Wizard

2. Specify the Excel File to Check

3. Specify the Expected File and the Data to Check

4. Close the Wizard

5. Run the Test

6. Analyze Results

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.

     

     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.

     

• Editing a script test –

  1. Open the script to be edited.

  2. Click on the Code Editor toolbar:

     

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

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.

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).

Validating an entire file

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

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.

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.

Click the image to enlarge it.

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

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

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:

Click the image to enlarge it.

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

In keyword tests

Click Run on the Keyword Test editor toolbar.

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.

Click the image to enlarge it.

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

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:

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.