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 HCL OneTest™ API. If your spreadsheet uses formulas, you must verify how data is being parsed by HCL OneTest™ API.

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 performance tests. To prevent any errors, ensure that the files are available on a local drive that can be accessed by HCL OneTest™ API.

  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 HCL OneTest™ API 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 HCL OneTest™ API 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 HCL OneTest™ API 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. HCL OneTest™ API does not dynamically recalculate the value. Workaround: Save the workbook again so that the last modified timestamp for the file is changed. HCL OneTest™ API recalculates with the updated value when you run the tests.