Skip to content

Using Excel Sheets to Write Test Cases

Although there are many ways of writing test cases for Botium, there are some good reasons why one would choose Excel for Botium Box:

  • Everyone knows Excel and how to use it, not only test automation engineers or developers

  • Can be shared with your peers by Email (could be done with Google Sheets as well …)

  • Test/Training data already available in Excel or CSV format

  • Mass-Edits of test cases

The downsides are mainly:

  • No generic way for version management as with plain text files, leading to files sent around named BotiumTests_v13.xls, BotiumTests_v13_remarks.xlsx … (better use Google Sheets)

  • Not all Botium features are available for Excel files

  • It is not possible to make any changes to the Excel content from within Botium Box

Excel File Structure

You can use this Excel file as a template:

Excel Sheet

Note

Details on the Excel format can be found here.

Using Worksheets

Botium encourages to use separate worksheets for structuring your content. The naming of the worksheets is important. Without any special configuration, Botium Box will look for

  • Convos in all worksheets with name containing “convo” or “dialog” and not “partial”

  • Utterance lists in all worksheets with name containing “utter”

  • Partial Convos in all worksheets with name containing “partial”

  • Scripting Memory in all worksheets with name containing “scripting” or “memory”

Make sure to meet these conventions in your Excel file.

Convos Format

You can write convos by having two Excel columns as in this example.

Attention

  • Botium will generate the test case names out of the sheet name and the line number where the conversation starts. You can use a 3-column format as well to have your custom test case names (see docs here).

  • Botium will by default expect exactly one header row (otherwise your test cases are screwed)

  • Column naming doesn’t matter

  • Separate two test convos with at least one empty row (otherwise your test cases are combined)

  • When starting a test case in the Bot column, Botium is actually waiting for the chatbot to begin the conversation. This is not supported by all Botium Connectors.

Utterances Format

You can write utterances lists by having two Excel columns as in this example.

Attention

  • Botium will by default expect exactly one header row

  • Column naming doesn’t matter

  • Start a new utterance lists by adding the reference name in the first column and use the second column to list the user examples

In order for the utterances lists to be used by Botium, you have to enable utterance exapansion in the Scripting Settings!

BotiumScript

You can use Botium Scripting in Excel files, using the magic words for utterances, asserters and basically all other BotiumScript structures.

When using multiple asserters on a single response from the Bot, make sure to place all BotiumScript in one cell:

Note

You have to add a line break within the cell to add BotiumScript asserters. Usually this can be done by using CTRL + ENTER when editing a cell.

If you are asserting on multiple bot responses, separate them into several cells:

You can as well use the utterance lists defined in an excel file in other file formats:

#me
HELLO_UTT

#bot
Hello, meat bag! How can I help you ?

Uploading the Excel to Botium Box

Use the Dropzone in the Test Sets menu in Botium Box to quickly upload the Excel file first time to Botium Box.

A separate test set for further refinement will be created and opened.

When you scroll down on the dashboard you should immediatly see the convos and utterances Botium Box extracted from the Excel file.

Making Changes

When heading over to the Test Case Repository you will see the uploaded Excel file in the list with the option to download it.

After making offline changes to the Excel file, re-upload it by dropping it to the Dropzone there. Dont' forget to click the Save button. Botium Box will show you a quick overview what content it identified in the Excel file to upload.

Run Test Cases from Excel file

The test set holding your Excel file can now be used as any other test set in Botium Box:

  • Use the Quickstart Wizard to select a chatbot channel, select the test set and let the test cases run against this chatbot. A Test Project will be created automatically to repeat any test session and to integrate Botium Box into your CI/CD pipeline.

  • In the Test Results menu, quickly select a chatbot channel and the test set to run a one-shot test session

Importing Test Cases from Excel to Botium Box

When following the steps from above, the Excel file is now linked with Botium Box, but it cannot be changed from inside Botium Box - you always have to download and upload it again. To have deeper integration with Botium Box you can as well import the Excel file content to Botium Box instead of just linking the Excel file. There are maybe good reasons for it:

  • After importing the Excel content, the visual test case designer in Botium Box can be used

  • The Excel content is converted to text-based test cases which can be versioned in a linked Git repository

  • When having a test case fail Botium Box can immediately point you to the troubled test case convo, instead of you having to open the Excel file and look it up there

There is one main downside: As soon as the test cases are imported into Botium Box, there is no way back. Any changes done to the Excel file won’t be reflected in Botium Box anymore. From then on the only way to make changes is in Botium Box.

Using the Transformation Wizard

In your test set where you uploaded the Excel file,head over to the Transformation Wizard and open the Test Set Splitter.

Note

The Test Set Splitter is a tool for splitting up a test set into two pieces for separation of training and test data, mainly for NLU analytics. But we can use it for our purposes here as well.

Set the Percentage for the first part to 100 percent and leave the others as is. After clicking the button there are two new test sets created - the first one contains all of our test cases from Excel (and the other one holds nothing and can be removed).

Heading over to the new test set will now show the convos and utterances in the visual test case editor.

Note

You will have to create a new Test Project with the Quickstart Wizard now to use the newly created test set for further test runs.