3.14.10. Using an Excel file as an external data source
Prev | 3.14. Working with test data | Next

### 3.14.10. Using an Excel file as an external data source

In the Properties View, you can add Excel files as a data source to Test Cases which contain parameters referenced from the Test Cases or Test Steps they contain.

| The addition of data via Excel is discouraged, because any problems with the data can only be found once the test is running. We recommend using the central test data sets in the ITE to manage data within the Project (Section 3.14.9, “Using central data sets”).

1. Navigate to the Properties View for the Test Case you want to add the Excel file to.

2. Enter the path to the Excel file in the Excel data file field.

   The path to the Excel file can be absolute or relative (if you have specified a data files path (Figure 3.32, “Preference Dialog”)).

   | The Excel file must be configured in a specific way in order it to be read (Section 3.14.10.1, “Configuring the Excel file”).

3. If you reuse this Test Case, the Excel file you enter will be reused along with the Test Case. When you reuse the Test Case, you can choose whether you leave this file or change it for another one.

   | If you store your Excel files in your workspace, you will be able to open these directly in the ITE from the navigator view using the in-place editor.

4. A Test Case with an Excel file as data is marked with a small Excel icon in the browsers to help you find it more easily later.

   | Please note that the Excel file is read at the start of the test execution. Any changes to the file after this will not affect the test data. For information on using the date function in Excel, see the section later Section 3.14.10.2, “Using the =TODAY() function in Excel”.

#### 3.14.10.1. Configuring the Excel file

- The worksheet in Excel can be named arbitrarily.

  Figure 3.23. Example Excel Table

  Only the first sheet will be used.

- Name the top cell of each column with a parameter name from your Test Case.

  For example, if you entered the reference ”=VALUE1”, then you must enter VALUE1 in the top cell of the column which will contain data for that parameter.

  | Values are case sensitive.

- Make sure that all the parameters for your Test Case have a column.

- Do not leave any gaps in the table.

- You must have an entry for each parameter used in the Test Case.

- You can then fill in the values or formulae you want to use for these parameters. Each row in the table represents one set of data for the parameters used.

  | We recommend that you format cells as text before adding the test data. This ensures that Excel's number formatting won't modify the test data in unexpected and undesirable ways. Especially for the boolean values true and false, make sure you format the column as text.

- If your Excel table contains data which change from day-to-day, then make sure you open the file before starting your test. Otherwise, the data from the last-opened state will be used.

  | If you store your Excel files in your workspace, you will be able to open these directly in the ITE from the navigator view using the in-place editor.

- See the section later on using the today function in Excel to get the current date (Section 3.14.10.2, “Using the =TODAY() function in Excel”).

- Excel files may not contain autofilters in any of the worksheets to be used as data sources. Remove any filters from all your Excel sheets before running a test.

#### 3.14.10.2. Using the =TODAY() function in Excel

1. Because Excel stores the ”=today()” function as a six-digit number, you must use a particular process to use this function to check a date as part of a test.

2. Enter the function =today() in a a different sheet to the one you are using for your data sets. You can enter it in the same sheet if you want to, but make sure that it has its own column. It must not be in one of the columns you will use as a data set.

3. For example, your =today() function is in sheet one, cell G4.

4. You want your date to appear as dd.mm.yyyy.

5. In the column for your data set, enter the following formula:

   > =text(Sheet1!G4, ”dd.mm.yyyy”)

6. This will mean that the date will be treated as it appears with Excel.

   | If you are using the =today() function, don't forget to open the Excel file before starting your test. Otherwise, the data from the last-opened state will be used.

   | If you store your Excel files in your workspace, you will be able to open these directly in the ITE from the navigator view using the in-place editor.