Configuring a Microsoft™ Excel data source

A Microsoft™ Excel data source can be used to populate tag values from a Microsoft™ Excel (.xls and .xslx) file.

About this task

Currently, not all formulas (used in spreadsheet cells) supported by Microsoft™ Excel are supported by Test Integrations and APIs. If your spreadsheet uses formulas, you must verify how data is being parsed by Test Integrations and APIs.

Procedure

  1. Click Browse to locate and select the Microsoft™ Excel file to use as the data source.

    If the test data source location is on a mapped network drive, you might get errors when running tests. To prevent any errors, ensure that the files are available on a local drive that can be accessed by Test Integrations and APIs.

  2. If the file contains multiple worksheets, enter the name of a valid worksheet to use in the Sheet name field. If this field is left blank, the first worksheet in the file is used.
  3. Use the Format Configuration to define the contents of the file (that is, if the worksheet contains a header row, and if there are rows to be skipped before and after the header row).
  4. Enable the Treat empty strings as null option if you want to treat empty fields in the data source as null.
  5. Enable the Treat text as null option if you want to specify a string field value that is to be replaced with null. For example, the file can contain "NULL" for fields with no value instead of using empty fields. In this case you would enter "NULL" in the text field, instructing Test Integrations and APIs to return a null value for such fields.
    Note: Treating empty fields or other field contents as null can be useful when you are using repeating elements in a data source, or when you are filtering test data. When you are previewing data, if enabled, null values (empty fields or text that is specified as such) is highlighted as shown in the image that precedes these steps (the highlight color can be specified in Test Integrations and APIs preferences).
  6. If Auto map new columns to tags at runtime is selected (the default value), then the values in any new columns that are added to the test data set are used as test data. This assumes that a tag exists that exactly matches each new column name and that the column count property is not set.
  7. If the number of test iterations can exceed the number of data rows, enable the Loop Data option to force Test Integrations and APIs to process the same rows over and over.
  8. Click Refresh to generate a preview of the data that are returned.
  9. Save the data source when finished, then see Creating tags from data source fields for details about copying column names to create tags from them.
    Note: If a cell in the worksheet has a formula that calculates a value based on the current time, the value is calculated only once when the data set is first accessed from a test or stub. Test Integrations and APIs does not dynamically recalculate the value. Workaround: Save the workbook again so that the last modified timestamp for the file is changed. Test Integrations and APIs recalculates with the updated value when you run the tests.