Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Importing test cases from excel sheets to TestRail


#1

Hi,

We have been using excel sheets for test cases, now we started using TestRail.
What are all the different ways to import the test cases from excel sheets to TestRail.

I have already read the information related to csv2TestRail. I’m looking for any other easier way to do this.


#2

I also have same query as Vijaya posted above…
Please share a way of importing test cases from excel sheets. Is there any specific format in which test cases can be imported?


#3

Hello,

Thanks for the feedback. It’s possible to import CSV / Excel files by converting the files to TestRail’s simple XML import file format. We have a conversion script for this available on our website here:

http://code.gurock.com/p/testrail-migrate/page/csv2testrail/

It’s usually needed to customize the script’s filter routine to adjust it to your particular CSV / Excel file layout (as different teams use different columns/rows for the data). The above mentioned page as well as the download contains examples for this. After converting the test cases you can import them on the test suite page.

If you have questions about the data migration or if you need help with this, please send us a sample file of your specific format via email (contact (at) gurock.com) and we are happy to suggest the best way to migrate the data.

Thanks,
Dennis


#4

Can you send me an excel file in the proper format for importing to TestRail? I have tried using the example file for simple import via the downloads on the support page, but am having no luck. I can successfully convert to XML, but I get an error on the import referring to section tags. I just want to see one work, so I can create a template for importing my excel test cases into testrail. Is there a video demo? (YouTube?)? or something of that sort? Is there a demo file that you are confident would successfully import into a fresh/clean test suite? Thanks for any guidance you can provide.

Theresa


#5

Hello Theresa,

Thanks for your posting. Could you let me know which error message you saw? The examples we ship with the CSV conversion script (in the ‘examples’ directory) work out of the box. If you have issues converting / importing the files I’m happy to provide additional details and assistance.

Thanks,
Dennis


#6

Hi Dennis, thank you for your explanations but I have a few questions:

  1. Is there any way to import back to the TestRail a few test cases previously exported from it to the CSV format?
  2. Does your conversion tool support test case IDs?
  3. Same question about keywords. Additionally, how they should be described in the custom_filter?
  4. How does import work with test cases’ IDs and keywords?

At the moment exported test case from Test Rail is in the following CSV format:
“ID”,“Title”,“Type”,“Priority”,“Estimate”,“Milestone”,“References”,“Preconditions”,“Steps”,“Expected Result”,“Keyword”,“Section”,“Description”,“Depth”,“Test Suite”
“C1”,“This is a test case”,“Other”,“4 - Critical”,"","","","","","",“Android
iOS
Windows Phone
”,“Home page”,"",“1”,“Master”

Thanks in advance,
Vladimir


#7

Hello Vladimir,

Thanks for your posting. We recommend using the XML export format if you want to re-import those test cases later (e.g. into another TestRail installation). The CSV export is not designed for re-importing data and mostly used for generating CSV/Excel reports.

Regarding your other questions:

  • The conversion tool also supports ID and keywords (multi-select I assume?). TestRail always uses its own case numbering but you can import additional IDs using a custom field:

http://docs.gurock.com/testrail-userguide/howto-fields

Custom fields can be imported via XML/our CSV migration tool as follows:

http://docs.gurock.com/testrail-userguide/howto-import#custom_fields

I hope this helps!

Regards,
Tobias


#8

Thank you Tobias, I saw all these guides but they don’t help too much.

I figured out that I need to add line below to have test case ID in the XML: $case[‘id’] = $row[0];

Yes, I created multi-select field called ‘keyword’ and updated custom_filter with the following line “$custom[‘keyword’] = trim($row[8]);” so I see my keyword (at least 1 of 3) in the XML but is does not import because it is multi-select field and requires an additional etc. XML structure within the tags. Now I’m trying to figure out how custom_filter code should look like to have following structure for output:


1
Android


2
iOS


3
Web

Kind Regards,
Vladimir


#9

Hello Vladimir,

You can use a multi-select custom field with the csv2testrail migration script as follows:

$custom[‘multi_select’] = array(
‘collection:item’ => array(
array(
‘id’ => 1
),
array(
‘id’ => 2
)
)
);

(This assumes that the multi-select field has a system name of ‘multi_select’, please change this accordingly to match your configuration)

This will generate an XML structure similar to:

.. 1 2 ..

I hope this helps.

Cheers,
Dennis


#10

Hi Dennis,

Thank you for your suggestion, it looks great but the only problem, how main CSV should look then. I had no idea how to use arrays in PHP so I just updated csv2testrail to avoid ‘<’ and ‘>’ transformation and started to use keywords as I wrote before. Additionally I changed transformation of ‘,’ to ‘;’ because some tests’ names contain commas.

Finally my scenario looks so:

  1. Update appropriate columns in XLS file with test cases’ details (Priority, section, subsection and test name)
  2. copy all these details to the notepad++, remove all tabulations and save as CSV
  3. Convert CSV to the XLM and verify everything is OK
  4. Import test cases to the TestRail.

Also I have a few additional questions:

  1. I see only 1 major thing after the import that sometimes newly created testcases are not added to the existing section and subsection but create a new ones. What can be the cause and how to avoid it?

  2. Test cases previously added to some section don’t move to another after the import with update (using appropriate IDs) - How to avoid it as well?

  3. Test cases’ numbers started as C1, C2, … for the first project. For the second project I figured out that test cases have IDs T1, T2, etc after the import. After I started to create test runs/plans and continued to import remaining test cases from the 2nd project: their IDs changed to C256, C257, etc. Is there any issue regarding this problem?

  4. Is there any way to move previously created test set to the appropriate test run?

  5. Is there any way to change test case status to Passed without opening window for ‘Add test Result’?

  6. Is there any way to update ‘Version’ and ‘Defects’ fields directly from test run and without opening window for ‘Add test Result’?

Thank you for your help in advance!

Best Regards,
Vladimir


#11

Hello Vladimir,

Thanks for the additional details. You can simply specify the array using the format Dennis mentioned and this looks as follows:

$custom['multi_select'] = array( 'collection:item' => array( array( 'id' => 1 ), array( 'id' => 2 ) ) );

Our script automatically generates the correct XML structure for this. We are also currently working on a direct CSV import and this will be made available with the next feature version of TestRail (this will make it much easier to import CSV/Excel files).

Please see below for your other questions:

      1. The XML import always creates new sections but you can choose to override/update test cases if you check the “Update existing test cases” option. You can simply move test cases from the new sections via drag & drop to your existing sections.
  1. The C# IDs are for test cases (on the Test Cases tab) and T# IDs refer to tests (on the Test Runs & Results tab). It’s normal that both IDs are incremented over time as you start adding new test cases as well as test runs/tests.

  2. You can simply change the case selection of existing test run to include additional cases (or use include-all to let TestRail automatically add cases to (active) test runs).

      1. You would currently need to use Add Test Result dialog for this but it’s already planned to reduce the number of steps required for this.

I hope this helps!

Regards,
Tobias


#12

Hi Tobias,

Thank you for your array but I assume it requires each keyword to be on a new line, right? anyway it did not work for me so I still use my scenario above as the most functional.

About other questions:
1-2. It is quite strange behavior that it creates new sections especially in case if these sections already exist.

  1. I spoke about test cases section only… Maybe I took a look to the Test Runs section at some point.

  2. You did not got my point: I created Test Set to run. After that I figured out that there will be many similar test sets and created Test Plan to have all related test sets there. Now I have 1 separate test set which I need to move to the Test Plan. Is there any way to do that?

5-6. Thank you that you have plans for this. Can you let me know what release will contain this functionality? I would like to push it to have in some near future.

  1. Also is there any possibility to have Replace All functionality for test runs? Do you have plans to implement it in some near future as well?

  2. I would like to see an appropriate columns such as defects and Version for every Test Set by default without updating columns manually. Is there any possibility to do that?

Thanks,
Vladimir


#13

Hello Vladimir,

Thanks for the update and please see below for my comments:

      1. We don’t want to make the import process too complicated for users to understand and adding new sections would be the desired standard behavior for most users. You can still rearrange/move your cases/sections around after importing them.
  1. The test case IDs will increment over time and they use a shared address space across projects. TestRail will also create internal test cases from time to time (e.g. when closing a test plan or run) and the IDs are not necessarily continuous in a test suite.

  2. Moving a test run to a test plan is currently not supported but you can simply add a new, empty test run to the test plan (this wouldn’t move the existing results though).

      1. We currently don’t have an estimate for this unfortunately but this is definitely one of the more popular feature requests.
  3. Do you mean a search & replace for text (e.g. for test results or test case fields)? That’s currently not available but I’m happy to add another vote to this feature request. You can already use the bulk-edit feature if you want to update multiple test cases.

  4. TestRail remembers the column settings per user & project and you would only have to configure this once (per user/project). The reason for storing the column configuration per project is that each project can have different columns/fields and it therefore makes sense to allow a different configuration per project. It’s already planned to look into supporting default column configurations per project.

Regards,
Tobias


#14

Thank you Tobias,

1-2. I see it quite complicated when I need to import some new test cases to the existing sections and then need to figure out what test cases from what section should be moved to another… Anyway I’d suggest to add a check during import - is there mentioned section exist and create it if it is not. (and ideal would be to have a checkbox during import process selected by default with “Don’t create existing sections”)

  1. OK, quite interesting… does that mean if I would like to re-arrange my test cases it would be quite hard to do? (e.g. to have TC 1-700 for 1st project, 701-2000 - to another, etc)

4 - 6. OK, I’d like to add this a a feature request so results will move to the test plan

  1. Yes, I would like to make a feature request for this as update multiple test cases is similar but nit the same as update multiple cases in the test run

  2. OK, configured it for me and explained to others how to configure. Unfortunately question is not really resolved as I would like to have some user I created to see test run results and would like to specify fields to show for this user - I would like to make a feature request for that as well.

  3. Another question about reporting: is there any way to have predefined emails for attachments?

Thanks in advance,
Vladimir


#15

Thanks for your additional feedback, Vladimir!

1-2. The import features are mostly meant for the initial import/migration of test cases but I can see that importing to existing sections can also be useful. I’ve just added another vote to this feature request, thanks!

  1. The IDs are automatically assigned and managed by TestRail and the address space is currently shared across projects.

4-7. Added your feedback to the feature requests, thanks!

  1. The columns can be configured by every user and it’s currently not possible to override or change the column configurations for other users. It’s already planned to add support for default column configurations.

  2. The email templates of TestRail cannot be modified currently easily but it’s also already planned to look into this. Or do you mean something else?

Regards,
Tobias


#16
  1. By the way, question was about some defect in the TestRail that don’t allow to move test case to another section while using import with Update option. In this case I need to review all 700+ test cases and move them to the appropriate sections manually…

  2. Does that mean that I will get a sorted list test cases (e.g. from 1 to 700) if I export my cases, update IDs (from 1 to 700) and upload test cases back (using TC update)?

  3. I meant to have per-defined reports’ recipients for separate projects so I don’t need to type their emails automatically. The same question is with reports’ default options that would be nice to configure once and don’t have to do it every time )

Regards,
Vladimir


#17

Hello Vladimir,

Thanks for your additional feedback.

  1. This is not a bug actually but TestRail wouldn’t change the section during the import, even with the update option enabled and only update the test case attributes. You can freely move your test cases after the import via drag & drop as needed.

  2. The IDs would get exported in the same order/hierarchy as on the test suite/cases page. You can then update them by leaving the IDs as is.

  3. Thanks for the additional details. You can rerun reports using the Create Similar button and this would automatically use the settings of the previous report (including notification list, etc.). It’s currently not supported to specify the default report options or notification list per project. If you create the same reports over and over again, you can also look into creating scheduled reports instead:

Regards,
Tobias