Back to QMetry All Products Help Page
Importing Test Cases from Excel File
- Mrunalini Potnis
Introduction
QMetry allows users to import test cases from an Excel file to create new test cases as well as to make changes in existing test cases in QMetry.
QMetry supports the seamless import of Excel files with multiple sheets. The import can create a new folder per sheet name and import the test cases accordingly. For example, there are 30 sheets in an Excel file with test cases on each of the sheets. The import will create 30 folders as per the Excel sheet names and import test cases into them if the Create folder with sheet name option is selected before starting the import.
Two separate Test Case templates are available to download and set your test case data within the corresponding template file:
- Test Case with Test Steps in Single Row
- Test Case with Test Steps in Multiple Rows (steps in individual rows)
The import wizard helps users import test cases directly into particular folders/sub-folders of the Test Case modules under the current project.
The test cases are imported in the same order that of in the Excel file, following are some of the reasons behind it.
1. In the organization test cases are ordered to produce the most efficient test flow. Hence, the QA Manager wants that the test case import order match the excel sheet.
2. A user creates a series of test cases in a logical order to be tested, one after another- and after creating those test cases the users want to import the excel file with the test cases in the same order as they are found in the excel file allowing them to be easily added to a test suite in the same order.
Best Practice: To carry out the import process smoothly, it is recommended that the size of file you upload should be a maximum of 10 MB.
First time Import of Test Cases from Excel File
First Time Import is when the organization does not have test cases in QMetry and they import test cases for the first time. This Import is creation of test assets for the first time. Import always creates version 1.
Convert Test Case data into QMetry Format
Download either of the Test Case templates provided below. Fill the template with your test case data that you want to import into QMetry. Enter comma separated values for fields with multi-lookup list. Make sure that all the columns in the Excel file have a corresponding field in QMetry. Majority of the columns in the Excel file should map to one of the QMetry fields. If there are columns which don’t really map, define those additional fields as Manage Fields in QMetry (Administration > Manage Fields).
Download Template
Note: The import will exclude any Graphs, Filters, Merge Cells and HTML Tags that exist in the Excel file.
1. Go to the Test Cases module.
2. Click on the cog icon on the toolbar and select Import.
It opens Test Case Import wizard showing the following three stages of import:
- Upload Source File
- Map Fields
- Select Import Folder
Step 1: Upload Source File
Before uploading the Excel file to import, define the format of test steps that exist in the file. Select either of the following options:
- Test Case Steps in Single Row: If a test case has multiple steps and all the steps are listed in a single row, then select the Test Case Steps in Single Rows option to upload the file for import.
- Test Case Steps in Multiple Rows: If a test case has multiple steps and each step is written in a separate row, then there will be multiple rows for multiple steps of a single test case. Select the option Test Case Steps in Multiple Rows to import such file.
Note: If you receive an “Invalid file” message while uploading some Excel file formats, then open the file in Microsoft Excel and save the file as type “Excel Workbook” with a different name. After that try to import the newly created file.
Browse the file and upload it to import.
The file name appears adjacent to the Choose File button.
- Select XLS Sheet: All the sheets that exist within the file are displayed on the drop-down. You can select multiple sheets that exist in the Excel file. Select at least one sheet that contains test cases that you want to import.
→ Importing Multiple Sheets: While importing multiple sheets, the field columns for field mapping are selected from the first sheet. For a successful import, ensure all the sheets have the same columns and format.
- Header Row: Mention the row number that is considered as a header row so that all rows after that header row can be imported.
Click Next.
Step 2: Mapping the Fields
The fields in the Excel file are displayed under Sheet Field and fields in QMetry are displayed under QMetry Field.
Map the fields from Excel and QMetry one by one. Fields mandatory to map are displayed in red color.
Removing mapping between fields
If you think that the mapping was not carried out properly, then you can remove the mapping of fields one by one.
To remove a single field mapping, open the QMetry Field list and select the "Select QMetry Field" option on it.
Step 3: Save Mapping
Before moving on, save your map so that if you ever need to re-import more test cases using the same file format you can avoid replication of the same mapping process. You can save the field mapping using the Save Map button and giving the map a name. The saved mapping is available to use the next time when you go for mapping the fields. Just select the mapping name and automatically mapping will be done based on the mapping criteria set in the selected mapping. It will apply the same mapping as you did.
Additional Mapping Options
- Auto Map: This option maps the system and custom fields automatically by using keywords in the header names.
- All Mappings: The drop-down lists out all the mappings saved by the logged-in user while importing test cases. If there is a Map Name on the drop-down, then it shows the mapping currently applied.
Click Next to proceed towards the next step for import.
Step 4: Selecting Import Folder
Select a destination folder on the tree in which you want to import the test cases. The “Selected Path” shows the destination folder path as soon as you select the folder on the tree.
If you want to create a new folder under the tree, then first select the location on the tree, enter the folder name in the text box and then click on the Add Folder button.
- Create folder with sheet name: Select this option if you want to create folders with Excel sheet names to import test cases. It will create a new folder for each sheet within the file and import test cases into the respective folders. It eases the identification of excel sheets to which the test cases belong.
- Import with Warning: The option allows users to import test case(s) with a Summary that exceeds 255 characters by truncating it to 255 characters. Select the check box to import test cases even with larger Summary. If the check box is not selected, the entire import will fail if there are test cases with a Summary exceeding 255 characters.
Click Finish to complete the Import wizard and start the import process.
After completing the import wizard process, the import will be scheduled. The Import Scheduled message will pop up and you can view the progress in the notification window on the application header.
This allows scheduled imports to run in the background and allows the user to continue with their other work.
Refer to Notification Window for Import Progress for more details.
Re-import of Test Cases from Excel File
Users can export test cases from QMetry, make required changes in the Excel file and re-import the file into QMetry. This re-import will update the existing test case details in QMetry. Re-import of test cases allows users to do bulk modification of already created test cases via excel. Re-import increases the existing version by 1.
You can export test cases from QMetry through Bulk Operations which provides options to Export as XLSX and Export as CSV. Refer to Exporting Test Cases for more details.
Step 1: Upload Source File
Before uploading the Excel file to import, define the format of test steps that exist in the file.
Select test case step format
Select either of the following options:
- Test Case Steps in Single Row: If a test case has multiple steps and all the steps are listed in a single row, then select the Test Case Steps in Single Rows option to upload the file for import.
- Test Case Steps in Multiple Rows: If a test case has multiple steps and each step is written in a separate row, then there will be multiple rows for multiple steps of a single test case. Select the option Test Case Steps in Multiple Rows to import such file.
Note: If you receive an “Invalid file” message while uploading some Excel file formats, then open the file in Microsoft Excel and save the file as type “Excel Workbook” with a different name. After that try to import the newly created file.
Browse the file and upload it to import. Select at least one sheet of the Excel file.
The file name appears adjacent to the Choose File button.
Mention the row number that is considered as a header row so that all rows after that header row can be imported.
Sheets that exist within the file are displayed in the Select XLS Sheet box.
Select the sheet that contains test cases that you want to import and click Next.
QMetry now allows blank column/header/sheet to upload because it is possible that an organization can have its first page containing name, logo or just intro instead of the actual test case itself.
Step 2: Mapping the Fields (Mandatory & Optional)
The fields in the Excel file are displayed in the Sheet Fields box and fields in QMetry are displayed in the QMetry Fields box.
- Map the fields from Excel and QMetry one by one.
- For Sheet Field at left, select its best match from QMetry Field.
Fields which are mandatory to be mapped.
- Entity Key Should be Mapped with ID
- Summary and All mandatory fields.
- If there is no change in Folder Paths, prefer not to Map it. Map only if there are changes to the folder path and you want to move assets from one folder to another
- Fields that are not mapped will be picked from the previous version.
Removing mapping between fields
- In case you think that the mapping was not carried out properly, then you can remove the mapping of fields one by one.
- To remove a single field mapping, open the QMetry Field list and select the "Select QMetry Field" option on it.
Step 3: Save Mapping
Before moving on save your map so that if you ever need to re-import more test cases using the same file format you can avoid replication of the same mapping process. You can save the field mapping using the Save Map button and giving the map a name. The saved mapping is available to use the next time when you go for mapping the fields. Just select the mapping name and automatically mapping will be done based on the mapping criteria set in the selected mapping. It will apply the same mapping as you did.
Additional Mapping Options
- Auto Map: This option maps the fields automatically by using keywords in the header names.
- All Mappings: The drop-down lists out all the mappings saved by the logged-in user while importing test cases. If there is a Map Name on the drop-down, then it shows the mapping currently applied.
Click Next to proceed towards the next step for import.
Step 4: Selecting Import Folder
A. Select a destination folder that you want to import the test cases into.
B. The “Selected Path” shows the destination folder path as soon as you select the folder on the tree. If you want to create a new folder under the tree, then first select the location on the tree and then click the New Folder button. Enter the Folder Name and click the " + " icon.
Click Finish to complete the Import wizard and start the import process.
After completing the import wizard process, the import will be scheduled. The Import Scheduled message will pop up and you can view the progress in the notification window on the application header.
This allows scheduled imports to run in the background and allows the user to continue with their other work.
Back to QMetry All Products Help Page