HINTS AND TIPS

Importing spreadsheets of tests into Testpad

Importing spreadsheets of tests into Testpad

Importing spreadsheets of tests into Testpad

Importing tests is the quickest way to get going in Testpad. For details on formatting considerations and how to import test cases from spreadsheets, start here.

Stef

By Stef

March 13, 2019

Linkedin Logo Twitter Logo Facebook Logo
t

he quickest way to get up and running with Testpad is to import existing tests, but the simplicity of doing this depends on the current formatting and layout of your tests. And this is especially true when you're copy/pasting from spreadsheets, which is what this article is all about. Read on for how this works in detail, but the impatient reader might like to skip ahead and take a close look at these Spreadsheet Import Examples.

This article concentrates on importing test cases from spreadsheets, but it should still be relevant to importing any source of table data into Testpad, including User Scenarios, Feature Definitions, or even existing checklists.

But if you're starting from scratch, writing new tests in your own style, then you don't need the help in here - instead, just get on with writing simple prompts of things you want to remember to test and go from there!

Per Script, use the Import Dialog to copy/paste text

Testpad's main document is a Script, and importing happens a Script at a time. Make a new empty Script and look to the Edit Menu -> Import option to open the Import Dialog.

Copy/paste your text for import, select a format option, and hit the Import button.

Import Menu

The goal for import is a single column of text

If you paste a simple block of text into the Import Dialog, on its Plain Text setting, then it will treat each line of text as a new row in the Script. For example:

Valid user name and password work
User can login immediately after changing password
Username with unicode characters works
User can login immediately after changing username
Invalid user name is rejected
Invalid password is rejected
Blank password is rejected
Blank username is rejected

will produce:

Import 5

Use indentation to give tests structure

Any leading spaces or tabs (i.e. indented) rows will be spotted and used to form title rows with blocks of (indented) content underneath. At a higher level, this is ideal for grouping tests by topic. At a lower level, it's ideal for grouping rows to describe richer test cases - more of which later.

Normal login
   Valid user name and password work
   User can login immediately after changing password
   Username with unicode characters works
   User can login immediately after changing username
Bad login
   Invalid user name is rejected
   Invalid password is rejected
   Blank password is rejected
   Blank username is rejected

will produce:

Import 4

Use the Spreadsheets format option when copy/pasting from spreadsheets

If you copy/paste from a spreadsheet, the format is typically quoted (to encapsulate cells, especially when multiline) with cells separated by Tabs.

The Import Dialog can deal with the quoted cells, but you need to specify the Spreadsheets formatting option. If you try to import a copy/paste from a spreadsheet using the Plain text format setting, the " quote characters won't be removed, and you're likely to get a bizarre looking layout.

Testpad only wants one column from a spreadsheet

When copy/pasting from a spreadsheet, you need to start by only copyinga single column. Tests in Testpad are essentially a single column of test descriptions, with only results (not additional test data) appearing in multiple columns.

So what about when your spreadsheet of tests, like most test plans, has tests defined in rows across multiple columns? Maybe you've got one column for the title, another for the Steps of the test, and yet another for the Expected Results.

How do you import two or three columns into Testpad's one column?

Well... the answer is that you don't! Instead, you need to play with your spreadsheet a bit to build a new (single) column that is some aggregation of the other columns.

This is best done by creating a new column, and starting in the first row, start building up a formula that will put together the other cells into a "Testpad-friendly" single cell.

For example, if we have three columns, something like this:

TitleStepsExpected Results
Basic login1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
Login succeeds and user's dashboard is displayed.
Rejected login1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
Login is denied with a message saying wrong username or password.

Then the trick is to create a fourth column, and fill it with formula like:

=CONCATENATE(B1,"
",B2,"
",B3)

The table will then look similar to:

TitleStepsExpected Results
Basic login1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
Login succeeds and user's dashboard is displayed.Basic login
1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
Login succeeds and user's dashboard is displayed.
Rejected login1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
Login is denied with a message saying wrong username or password.Rejected login
1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
Login is denied with a message saying wrong username or password.

Now if we select the 4th column, and copy/paste that into Testpad, we get:

Import 6

But we can do a bit better if we add in some indentation to better structure the new test column.

Consider a modified version of the formula from above:

=CONCATENATE(B1,"
    ",B2,"
    ",B3)

Now our table's fourth column looks a bit different:

TitleStepsExpected Results
Basic login1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
Login succeeds and user's dashboard is displayed.Basic login
    1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
    Login succeeds and user's dashboard is displayed.
Rejected login1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
Login is denied with a message saying wrong username or password.Rejected login
    1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
    Login is denied with a message saying wrong username or password.

This isn't quite what we want, as only the first step has been indented. So getting a bit more clever with the formula, replacing newline characters for newline+spaces fixes this:

=CONCATENATE(B1,"
    --",REGEXREPLACE(B2,"\n","
    --"),"
    => ",B3)

Now our table's fourth column looks tidier on the indentation

TitleStepsExpected Results
Basic login1. Load the /login page
2. Enter valid user name
3. Enter valid password
4. Press enter
Login succeeds and user's dashboard is displayed.Basic login
    --1. Load the /login page
    --2. Enter valid user name
    --3. Enter valid password
    --4. Press enter
    => Login succeeds and user's dashboard is displayed.
Rejected login1. Load the /login page
2. Enter valid user name
3. Enter an invalid password
4. Press enter
Login is denied with a message saying wrong username or password.Rejected login
    --1. Load the /login page
    --2. Enter valid user name
    --3. Enter an invalid password
    --4. Press enter
    => Login is denied with a message saying wrong username or password.

This latest version of the formula also inserts some "--" prefixes which Testpad interprets as "comment" (non-test) rows.

Whether you want to use comment rows depends on whether you mind collecting results for "steps". My own preference is it's fine, when I record a "pass" against a step, I'm recording a result to the effect "yes, did that, no problems". But lots of people like to make steps just comments and only record results against the Expected Outcomes.

And when this modified 4th column is copy/pasted into Testpad, we get something much more workable:

Import 7

And getting more complicated...

Of course, your spreadsheet might have many more columns. Take for example something like this:

Table 7

Which can be imported, as above, with a new column that aggregates (with modification for prefixes and indentation), to produce:

Import 2

How did that work?... check out the collection of Spreadsheet Import Examples for more detail.

These examples all illustrate multiple test cases per import - it's a common misuse of Testpad to only have one test case per script - you definitely want to put multiple test cases into each script, making good use of the indentation structure to form logical groups.

Copy/paste two or three columns to set Tags and Notes

Testpad will actually accept two or three column imports, but it uses them for it's own purposes... if present, Column 2 will define the Tags to include for a tow, and Column 3 will define Notes.

Tags are part of Testpad's tags and filtering feature, where test rows can be tagged with labels that Test Runs can filter for - useful for defining subsets of tests for certain test run conditions.

And Notes are for the notes field that is "behind" every test row, displayed in the Test Details dialog. You can access the Test Details dialog by clicking on the small doc icon, or double-clicking the row ID, or typing Alt-N. These Notes are wiki-formatted and can be displayed during testing if required using the little checkbox in the Test Run execution dialog.

The Notes field (third column in multi-column imports) is another option for where to put imported test content. For example, if most of your testers are familiar with the product, then you might want to "hide" the Steps in the Notes field and only display the things to Verify in the main part of the Script. New or less experienced testers can then open the Details dialog to get the extra instruction when needed, but for everyone else, the Script is more compact and can be read through and executed more quickly.

To populate the Notes field during an import then, you need your copy/paste to span three columns, where the first column will be the main test text, the middle column empty (assuming you're not trying to tag anything yet), and the third column for the notes. This is all best achieved by adding three new columns to your spreadsheet, and setting up formulas to aggregate/copy the relevant cells into the first and third of the new columns.

Again, have a look at the Spreadsheet Import Examples for an example of a multi-column import.

CSV (Comma-Separated Values)

And lastly, a note on CSV imports. These work exactly the same as spreadsheet imports, except that the cells are separated by commas instead of tabs.

Just as for copying from spreadsheets, you need the first column to be the main test text as described (at length!) above, the second column for tags, and the third column for each test's Notes field.

Want some help?

If this is all still not making sense, or you have existing tests that you'd like some help with how to import, then don't hesitate to email some example tests and questions to support@testpad.com - always very happy to help.

Green square with white check

If you liked this article, consider sharing

Linkedin Logo Twitter Logo Facebook Logo

Subscribe to receive pragmatic strategies and starter templates straight to your inbox

no spams. unsubscribe anytime.