Once you setup testlink, the first question comes in mind that how do I upload my existing test sheets into Excel. Tetslink 1.9.2 supports uploading excel sheets but before that you need to create few sample tests into testlink and then export it and then use the imported xsd to convert your excel sheet. For me, its quite cumbersome process and I wanted that we have some sort of upload functionality so that my QA team don't have to make much effort to upload their existing tests. I found a utility which is php based and supports excel upload by a simple web interface. the utility can be downloaded from http://code.google.com/p/testlink-1-9-xls-test-case-uploader/downloads/list
This utility was great but it was limited to few columns of excel but I needed additional columns plus some small modification in the existing column. With reference to their php, I modified it to support columns as per our requirement. Here is the modified version of their php.
This modified version of utility supports following columns
||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||
Few Notes:
|
<?php
// Test CVS
// Vishnu Agrawal
ini_set("display_errors", "1");
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set('America/Los_Angeles');
if($_FILES['xls_file']['tmp_name'])
{
move_uploaded_file($_FILES['xls_file']['tmp_name'],'uploads/'.$_FILES['xls_file']['name']);
$xls_file = 'uploads/'.$_FILES['xls_file']['name'];
include('../config_db.inc.php');
mysql_connect(DB_HOST,DB_USER,DB_PASS);
mysql_select_db(DB_NAME);
require_once 'Excel/reader.php';
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('CP1251');
/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/
/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/
/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/
$data->read($xls_file);
/*
$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
*/
$values = '';
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
$values[] = $data->sheets[0]['cells'][$i];
}
/*echo '<pre>';
print_r($values);*/
$rownum = 0;
$case_count = 0;
$test_new = 0;
$test_update = 0;
$test_add_fail = 0;
foreach ($values as $colnum)
{
$rownum++;
if($rownum > 1)
{
$excel_project_name = trim($colnum[1]);
$excel_suite_name = trim($colnum[2]);
$excel_tc_title = trim($colnum[3]);
$excel_tc_summary = trim($colnum[4]);
$excel_pre_condition = trim($colnum[5]);
$excel_tc_step = trim($colnum[6]);
$excel_tc_result = trim($colnum[7]);
$excel_tc_priority = trim($colnum[8]);
$excel_tc_keywords = trim($colnum[9]);
if (!$excel_project_name) {
if($excel_suite_name && $excel_tc_title) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink project name is not Specified</font>\<br\>");
$test_add_fail++;
}
continue;
}
if (!$excel_suite_name) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink Suite name is not Specified</font>\<br\>");
$test_add_fail++;
continue;
}
if (!$excel_tc_title) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink tese case title is not Specified</font>\<br\>");
$test_add_fail++;
continue;
}
// Get Project id - Start
$query_project = mysql_query("SELECT testprojects.id, nodes_hierarchy.name FROM testprojects, nodes_hierarchy WHERE testprojects.id = nodes_hierarchy.id AND nodes_hierarchy.name='$excel_project_name'");
if(mysql_num_rows($query_project) != 1) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_project) . " test project found with name <b>$excel_project_name</b></font>\<br\>");
$test_add_fail++;
continue;
}
$res_project = mysql_fetch_assoc($query_project);
$project_id = $res_project["id"];
//echo " Project id is " . $project_id . "\<br\>" ;
// Get Project id - End
// Get test Suite id - Start
$query_testsuitenames = mysql_query("SELECT TS.*, NH.name, NH.parent_id FROM testsuites TS JOIN nodes_hierarchy NH ON NH.id = TS.id WHERE NH.name = '$excel_suite_name'");
$count = 0;
$suite_id = 0;
while($suites = mysql_fetch_assoc($query_testsuitenames)) {
$query_get_project = mysql_query("SELECT _id FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM nodes_hierarchy WHERE id = _id) AS parent FROM ( SELECT @r := $suites[id] ) vars, nodes_hierarchy h WHERE @r IS NOT NULL) AS t WHERE t.parent IS NULL");
$res_get_project = mysql_fetch_assoc($query_get_project);
if(count($res_get_project) == 1) {
if($res_get_project[_id] == $project_id) {
$count++;
$suite_id = $suites[id];
}
}
}
if($count != 1 && $suite_id ==0) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . $count . " suite name found with name <b>$excel_suite_name</b> </font>\<br\>");
$test_add_fail++;
continue;
}
//echo " suite id is " . $suite_id . "\<br\>" ;
// Get test Suite id - End
$priority_str = $excel_tc_priority;
$priority_id = 2;
if (strcasecmp($priority_str,"Low") == 0) $priority_id = 1;
if (strcasecmp($priority_str,"High") == 0) $priority_id = 3;
$query_test = mysql_query("SELECT DISTINCT NH_TCASE.id,NH_TCASE.name,NH_TCASE_PARENT.id AS parent_id, NH_TCASE_PARENT.name AS tsuite_name, TCV.version, TCV.tc_external_id, TCV.id AS tcversion_id FROM nodes_hierarchy NH_TCASE, nodes_hierarchy NH_TCASE_PARENT, nodes_hierarchy NH_TCVERSIONS, tcversions TCV WHERE NH_TCASE.node_type_id = 3 AND NH_TCASE.name = '$excel_tc_title' AND TCV.id=NH_TCVERSIONS.id AND NH_TCVERSIONS.parent_id=NH_TCASE.id AND NH_TCASE_PARENT.id=NH_TCASE.parent_id AND NH_TCASE_PARENT.id = $suite_id AND NH_TCASE_PARENT.node_type_id = 2 ORDER BY TCV.version desc LIMIT 1");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When selecting suite id\<br\>";
$test_add_fail++;
continue;
}
$res_test = mysql_fetch_array($query_test);
if(count($res_test)>1)
{
//Test case already exists in the test suite, then we need to just update it
$testcase_id = $res_test["id"];
$testcase_version_id = $res_test["tcversion_id"];
mysql_query("UPDATE tcversions SET importance = $priority_id, summary = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."', preconditions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."' WHERE id = $testcase_version_id");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE tcversions for row $rownum ...\<br\>";
$test_add_fail++;
continue;
}
if($excel_tc_step||$excel_tc_result)
{
$query_test_step_parent = mysql_query("SELECT TCSTEPS.id, MAX(TCSTEPS.step_number) AS max_step FROM tcsteps TCSTEPS JOIN nodes_hierarchy NH_STEPS ON NH_STEPS.id = TCSTEPS.id WHERE NH_STEPS.parent_id=$testcase_version_id");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When select test case steps for row $rownum ...\<br\>";
}
else {
$res_test_step_parent = mysql_fetch_array($query_test_step_parent);
mysql_query("UPDATE tcsteps SET actions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."', expected_results = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."' WHERE id = $res_test_step_parent[id]");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE test case steps for row $rownum ...\<br\>";
}
}
}
if($excel_tc_keywords)
{
//First remove all existing keywords of this test and then add again
mysql_query("DELETE from testcase_keywords where testcase_id = $testcase_id");
$keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
foreach ($keywords as $keyword) {
$key = trim($keyword);
$query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
$res_keyword = mysql_fetch_assoc($query_keyword);
$keyword_id = $res_keyword["id"];
if($keyword_id){
mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($testcase_id,$keyword_id)");
}
}
}
$test_update++;
}
else {
//This is a new test and we need to add it in test suite
//*** Start Transaction ***//
mysql_query("BEGIN");
$objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order,name) VALUES ($suite_id,3,100,'".mysql_real_escape_string($excel_tc_title)."')");
$case_id = mysql_insert_id();
$objQuery2 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_id,4,0)");
$case_des_id = mysql_insert_id();
$objQuery3 = mysql_query("INSERT INTO tcversions (id,importance,summary,preconditions) VALUES ($case_des_id, $priority_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."')");
if(($objQuery1) and ($objQuery2) and ($objQuery3)) {
//*** Commit Transaction ***//
mysql_query("COMMIT");
}
else {
//*** RollBack Transaction ***//
mysql_query("ROLLBACK");
echo "ERROR :: Rollbacked transaction of new tests for row $rownum ...";
$test_add_fail++;
continue;
}
if($excel_tc_step||$excel_tc_result)
{
//*** Start Transaction ***//
mysql_query("BEGIN");
$objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_des_id,9,0)");
$case_step_id = mysql_insert_id();
$objQuery2 = mysql_query("INSERT INTO tcsteps (id,actions,expected_results) VALUES ($case_step_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."')");
if(($objQuery1) and ($objQuery2))
{
//*** Commit Transaction ***//
mysql_query("COMMIT");
}
else
{
//*** RollBack Transaction ***//
mysql_query("ROLLBACK");
echo "ERROR :: Rollbacked transaction of test step updation for row $rownum ...";
}
}
if($excel_tc_keywords)
{
$keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
foreach ($keywords as $keyword) {
$key = trim($keyword);
$query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
$res_keyword = mysql_fetch_assoc($query_keyword);
$keyword_id = $res_keyword["id"];
if($keyword_id){
mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($case_id,$keyword_id)");
}
}
}
$test_new++;
}
}
}
$case_count = $test_new + $test_update;
echo "<font color=#993300>Total <b>$case_count</b> test cases updated successfully.</font>\<br\>";
echo "<font color=#0000ff>New tests : <b>$test_new</b></font>\<br\>";
echo "<font color=#0000ff>Tests updated : <b>$test_update</b> </font>\<br\>";
echo "<font color=#0000ff>Tests update failed: <b>$test_add_fail</b> </font>\<br\>";
}
?>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testlink XLS Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">
<table>
<tr><td>Use this page to import test cases from Excel sheet to Testlink, Excel file should have following columns:</td></tr>
</table>
<table border="1">
<tr>
<th>Test Project Name from Testlink</th>
<th>Test Suite Name from Testlink</th>
<th>Test Case Title</th>
<th>Test Case Summary</th>
<th>Pre Conditions</th>
<th>Step Actions</th>
<th>Expected Results</th>
<th>Priority</th>
<th>Keywords</th>
<th>Test Status</th>
<th>Testplan Name from Testlink</th>
<th>Build Name from Testlink</th>
<th>Comments (can be used to specify Bug#)</th>
<th>Username (Testlink login name)</th>
</tr>
</table>
<p>
<table>
<tr>
<th align="left">NOTE:</th>
</tr>
<tr>
<td>
<ol>
<li>Tests should be in the first sheet of the excel file</li>
<li>First row will be ignored as it will have column headers</li>
<li>Test Project name should exactly match with Testlink project name in which you want to upload your tests. </li>
<li>Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. </li>
<li>Test Case Title is the title which will be used to list test in the testlink tree</li>
<li>Multiple keywords are supported (comma separated)</li>
<li>Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.</li>
<li>Test Status, Test Plan, Build Name, Comments and User Name columns are ignored as they are used to update test results</li>
<li>If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated</li>
</ol>
</td>
</tr>
</table>
<table>
<tr>
<td><b>Testlink XLS Uploader:</b></td>
</tr>
<tr>
<td><input type="file" name="xls_file" /> <a href="pub_dashboard.xls">Sample file format for testcase</a> </td>
</tr>
<tr>
<td><input type="submit" name="submit" value="submit" /> </td>
</tr>
<tr>
<td></td>
</tr>
</table>
</form>
</body>
</html>
Posts
7 comments:
again,.. great job.. test cases are added to the DB but are not displayed on the UI.. reason:
adding null values in the query for updating nodes hierarchy table.... check this out..............
I have extended a perl script to do this however,,,,,
Hello Vishnu,
It seems not to be working in our Testlink 1.9.2.
When i want to upload i get the message:
Test result upload for test row 2 failed. REASON: 0 suite name found with name importtest
Test result upload for test row 3 failed. REASON: 0 suite name found with name importtest
In the DB these testsuites exist!
Please help.
Solved: Copy paste error with the script.
Everything workes now.
remarks:
1.There is a max of char that can be inserted via xls into steps/actions.
2.The column steps/actions is imported in one step only not in more steps when you have input like 1. step 1
2. step 2 etcetc.
Hello vishnu,
Thank you for benefical article.
Could you please add xls file for example.
TolgaE
It doesent work on 1.9.2 the steps and summary doesent get copied to testlink
any idea
Are you able to import custom fields with this modified version? Many thanks
Post a Comment