Starting with TestRail 4.1, TestRail now includes a direct Excel/CSV import which supports a variety of Excel/CSV formats. This is now the recommended way to import simple Excel/CSV files and you can learn more about this import option here: TestRail Excel/CSV import
This script can be used to convert existing test cases in CSV files to TestRail. You can also convert Excel files by saving them as CSV files first (to do this, select File > Save As in Excel).
Because there is no standard format on how to store test cases in CSV / Excel files, you will need to tell the script how to extract your data. To do this, you can write a simple filter routine that will be called by the csv2testrail script to extract the actual data from your CSV files.
Once you converted the data, you can import the test cases and sections into TestRail. To import the converted XML file into TestRail, create an empty test suite, and choose the import button from the toolbar.
Download
You can download the latest version of the CSV & Excel migration script here:
TestRail CSV Migration
A script to migrate your CSV & Excel files to TestRail
Usage
> php csv2testrail.php <filter> <input-file> <output-file> [mode] [delimiter]
<filter-script> a PHP script to extract the CSV data for conversion.
<input-file> should be the filename of a CSV file with test cases
you want to convert (for example, an exported Excel file).
<output-file> specifies the filename of the resulting TestRail
import/export file.
[mode] An optional mode. The following modes are available:
--export The default behavior; exports the data to the XML file.
--csv For debugging: prints the CSV data as seen by the script
--cases For debugging: prints the cases after the filter script
was called
--tree For debugging: prints the section/case tree after analyzing
the cases and sections
[delimiter] Allows you to override the default comma delimiter.Custom Filter
As CSV files don’t have a standard layout, you need to develop a small filter routine to do the actual data extraction. This is needed as CSV / Excel files differ: company A might store the case title in column 1, company B might store the title in column 5. Other CSV files might even use multiple lines for test cases and so on.
By using a filter routine, the csv2testrail script is very flexible and can work with almost all CSV and Excel files. The filter routine is written in PHP and is called by the csv2testrail script. You specify the PHP script with your custom filter routine as the first argument when you execute the csv2testrail script;
The routine has a very simple format. You just need to specify a function called custom_filter. The CSV data is passed as the first and only parameter. The $csv parameter is an array of rows found in the CSV file. Each row is an array of columns. Here’s what a typical custom_filter.php script looks like:
function custom_filter($csv)
{
// Skip the first line
unset($csv[0]);
// Iterate rows and build cases
foreach ($csv as $row)
{
// Create a new case and assign the various properties
$case = array();
$case['title'] = $row[0];
$case['type'] = $row[1];
$case['priority'] = $row[2];
// Custom fields, such as Preconditions, Steps and
// Expected Results
$custom = array();
$custom['preconds'] = $row[3];
$custom['steps'] = $row[4];
$custom['expected'] = $row[5];
$case['custom'] = $custom;
// The export script will automatically create the section
// hierarchy for us. All we have to do is to specify the
// sections for a test case in this format:
//
// "Section 1 Section 2 Section 3"
$case['section'] = $row[6];
// Add the cases to the list
$cases[] = $case;
}
// Return all cases
return $cases;
}The custom_filter function must return an array of cases. Each case consists of an associative array specifying the case details. For example, the $case['title'] array element should specify the title of a case. You can also assign arrays as case properties. All arrays and values will be converted to XML. For example, to specify the custom element of TestRail XML files, simply assign an array to $case['custom']. A case can thus look like this:
[35] = Array
(
[title] = Verify line spacing on multi-page document
[type] = Other
[priority] = 2
[custom] = Array
(
[preconds] = Suspendisse ..
[steps] = In vulputate ..
[expected] = Massa sodales ..
)
[section] = Customization
)The csv2testrail script can automatically build the section and case hierarchy for you. You simply need to specify the section path for each case. For example, to add a case to section Bar, which is a subsection of section Foo, simply specify the $case['section'] element like this:
Foo > BarBecause the script allows you to map the CSV data to cases in whichever way you need, you can also easily convert any data types on-the-fly. For example, if your CSV / Excel file contains a column named Test Type with values such as MANUAL and AUTO, you could easily convert those values to TestRail’s built-in type with values such as Functional and Automated. Please see the Advanced example on how to do this.
Troubleshooting
The script supports a few options that are useful to troubleshoot your filter routine. Specifically, the optional [mode] argument can be used to output the internal data structures of the migration script at various stages of the script execution. The following options are available:
| Argument | Description |
|---|---|
| –export | The default behavior; exports the data to the XML file and doesn’t output any extra information. |
| –csv | Prints the CSV data as seen by the script. This is especially useful to find out the $row indices of the various data columns, as the printed data is the exact data that is passed as the $csv parameter to your filter routine. |
| –cases | Prints the cases after the filter script was called. This is the exact data that was returned by your filter script. |
| –tree | Prints the section/case tree after the section value of the cases were analyzed. |
Examples
The following examples are included in the download archive:
Simple
This example comes with a simple CSV file and matching filter script. The CSV file contains various test case fields such as the title, type, steps, and so on. The filter script extracts the data and returns the cases in the correct format. To convert the sample CSV file to TestRail’s XML file format, simply execute the following command from the examples\simple directory:
> php ../../csv2testrail.php custom_filter.php testdata.csv suite.xmlAdvanced
The Advanced example comes with a CSV file and matching filter script. Similar to the Simple example, the CSV file contains the test case data. However, some test cases use multiple lines to specify multiple test step lines. Additionally, the CSV file contains a field called Priority that needs to be converted to TestRail’s built-in priority format (which is numeric). The filter routine handles both issues and correctly converts the CSV file to TestRail’s XML file format.
To convert the CSV file, simply execute the following command from the examples\advanced directory:
> php ../../csv2testrail.php custom_filter.php testdata.csv suite.xmlSteps
This example is similar to the simple example above, except that it uses the steps custom field type for the test case steps. To convert the sample CSV file to TestRail’s XML file format, simply execute the following command from the examples\simple directory:
> php ../../csv2testrail.php custom_filter.php testdata.csv suite.xml