Thursday, July 4, 2013

Requirements and Test Cases import to Testlink 1.9.x - Excel VBA Macro updated

Hi All,

After my hastened efforts to make a basic working macro for Testlink 1.9.x, I have taken some time to fix atleast a few of the issues.

You can find the latest macro here:
https://docs.google.com/file/d/0BzBCa6US6n8TV0ZTLXlZa25vcUk/edit?usp=sharing

In addition to what is supported that was mentioned in my previous post, here is a list of notes to give you an idea on why it might NOT be working for you.
  1. You need to have either Requirement Specification (Case Insensitive) or  Requirements(Case Insensitive)  in the first row of Requirements (Case Sensitive) worksheet.
  2. Under the Requirement Specification merged column header, you MUST have BOTH DocID (Case Insensitive) and Title (Case Insensitive) Columns.
  3. Under the Requirements merged column header, you MUST have BOTH DocID (Case Insensitive) and Title (Case Insensitive) Columns.
  4. You need to have either Test Suite (Case Insensitive) or  Test Cases (Case Insensitive) in the first row of TestCases (Case Sensitive) worksheet.
  5. Under the Test Suite column header, you must have Name (Case Insensitive) column. Details column is optional, including custom fields.
  6. Under the Test Cases column header, you must have Name (Case Insensitive) column. All the other columns are optional including custom fields.
  7. You can change the name of the Requirements Worksheet by changing the value of variable ReqSheet = "Requirements".
  8. You can change the name of the TestCases Worksheet by changing the value of variable TestSheet = "TestCases".
  9. You can change the name of all other columns by checking under all the SELECT CASE blocks. Please use only UPPER CASE letters in the macro, even though you may have used any case in your excel sheet.
  10. Once the macro completes running, you'll get a message box as follows.


  11. If one of the files were NOT generated, the error message will be displayed as follows.


  12. Please ignore the error if you do not have the "Requirements" worksheet or "TestCases" worksheet intentionally.
  13. Otherwise, check the column names in the worksheet where the error was reported.
  14. After generating the XML successfully, please open the XML on IE to ensure that the XML is NOT broken because of special characters like double quotes ("), angled brackets (<, >), etc. If so, please remove/replace these special characters and rerun the macro. Please note that this is a limitation of the XML and NOT the macro. You can replace these special characters by using the following table.
Character NameEntity ReferenceCharacter ReferenceNumeric Reference
Ampersand&&&#38;
Left angle bracket<<&#60;
Right angle bracket>>>
Straight quotation mark""'
Apostrophe''"

Please make sure to open the macro in IE, and make sure it opens up correctly, before you try to import it to Testlink.
Posted by at

23 comments:

  1. AnonymousNovember 13, 2013 at 8:19 AM

    Thanks so much, your macro is really usefull and I stopped wasting my time perfecting my macro

    ReplyDelete
  2. UnknownNovember 29, 2013 at 7:03 AM

    Thanks a lot!
    Worked like a charm, but got in troubles when I tried to enable custom fields... TL custom fields definition isn't that clear about the "enable on context", zand therefore I'm stuck with this error message when trying to import after custom fields definition:

    Test Case : TS 2 TC 1 : Already exists, data of LAST version has been updated.
    Test Case : TS 2 TC 1 : Custom Field values are present on import file but can not be imported because there are no Custom Fields with Test Spec. Design Scope on this Test Project

    ... (and so on for all defined test cases)

    Any hints about this?

    ReplyDelete
    Replies
    1. UnknownNovember 29, 2013 at 7:08 AM

      OK, my bad... just forgot to assign custom fields to my project.

      RTFM !

      Delete
  3. UnknownDecember 12, 2013 at 5:48 AM

    Back again ; )
    Any hints about how you would transform your tables in your sheets, in order to havec a more dynamic document (filtering on Reqs and TestCases etc)?
    Defining the current static cells area as tables only breaks down your whole work : (

    It could really become handy for long requirements / tests lists...

    ReplyDelete
  4. UnknownFebruary 12, 2014 at 9:41 PM

    This comment has been removed by the author.

    ReplyDelete
  5. UnknownFebruary 12, 2014 at 9:46 PM

    One bug on custom fields, need to move the following code out of the "for" loop (together with the print statement following it), put it before the "for" loop:
    xmlStr = strTab & ""

    ReplyDelete
  6. Saayid RahimJune 1, 2014 at 11:52 PM

    Hello Gowri,

    Excellent Tool... One question is if i have multiple custom fields how can i use the tool... i.e 2-3 custom fields in Test Suite/Test Case or Requirements..

    ReplyDelete
    Replies
    1. GowriJune 2, 2014 at 5:44 PM

      Hi Saayid, you can add more custom fields by just adding extra columns in the spread sheet with the custom field name as column name.

      Regards,
      Gowri

      Delete
    2. Saayid RahimJune 2, 2014 at 9:58 PM

      Hi Gowri,

      Thanks for the super quick response. I did give a try adding extra cust field column in Requirement specification and when i run the macro i get the pop up 'Please check the column names in Requirements Sheet' and the xml does not get generated.

      Any pointers as to how this can be resolved?

      Any macro code changes required?

      I read many posts and one of your prior posts i read that for an older version of the macro up to 10 cust fields are allowed.

      Appreciate your timely response and incredible support for the tool!

      Thanks and Good day Gowri!


      Delete
  7. CalviciesSeptember 5, 2014 at 2:52 AM

    Great macro!
    One question, is there any possibility to add more "name" and "details" fields to make a tree?
    something like:

    Name Details Name Details
    1stlevel1 1stlevel1 2ndlevel1 2ndlevel1
    1stlevel1 1stlevel1 2ndlevel2 2ndlevel2
    1stlevel2 1stlevel2 2ndlevel1 2ndlevel1
    1stlevel3 1stlevel3 2ndlevel1 2ndlevel1
    1stlevel4 1stlevel4 2ndlevel1 2ndlevel1

    I was "playing" with the xml, and if you put a name in the upper , testlink create a tree.


    .
    .
    .
    .

    ReplyDelete
    Replies
    1. CalviciesSeptember 5, 2014 at 3:17 AM

      I meant
      "< testsuite name="1stlevel1" >"
      "< testsuite name="2ndlevel1" >"

      Delete
  8. AnonymousOctober 3, 2014 at 6:22 AM

    hi, I would like to ask where would I find the tool to use this latest macro, or should I say how would I generate XML file out of this marco ?

    I downloaded the marco, but could not find the way to generate xml file out ofi it.

    Thanks in advance!

    ReplyDelete
    Replies
    1. AnonymousNovember 19, 2014 at 9:00 AM

      In Microsoft Excel, click View > Macros in the menu. It will open a window with a macro called "genreqNtcXML." Run it and will generate two XML files.

      Delete
  9. AnonymousNovember 22, 2015 at 2:53 AM

    Hi Gowri,

    this is a wonderful tool. i have couple of questions. i am using TestLink 1.9.14 version.

    1) There will be two XML files generated. a) for requirements b) for test cases. I need to deactivate the XML file generated for requirements. How do i do it in the source code?

    2) i need to deactivate some coulumns in "testcases" excelsheet like "CUST_TS, CUS_TC (if i am not wrong they are custom fields), all columns in test suite and Requirements".

    3) The example excel sheet with values which is at the top of the page generates an XML file but if i enter my real data the macro doesn't generate an XML file. When i opened it in IE there were no tags found but just the data i entered. Do you know where the problem lies?

    ReplyDelete
    Replies
    1. GowriNovember 22, 2015 at 3:11 AM

      1. Just delete the requirements worksheet.
      2. Delete columns that you dont need.
      3. When you click run macros the xml files will be generated. You have to open the xml file (not the xls file) with IE.

      The first blog has detailed step by step instructions. This secong blog has updated macro and troubleshooting tips. Please refer to both.

      Delete
  10. AnonymousNovember 23, 2015 at 1:53 AM

    Hi Gowri,

    thanks for the rapid reply. Questions one & two are answered.

    For question 3: i have been opening the XML file & not xls file. Still there were problems

    I was reading the blog from May, 2103 where a note from you states at the bottom which is pasted here below for the reference:

    "NOTE: If you are importing test cases for a single testsuite or multiple testsuites within an already existing test suite, you need to edit the test cases XML file and remove the following entries :
    The second line in the testcases XML, which has the exact text as below:

    and the last line in the same XML, which has the exact text as below: "

    This is my exact scenario. i.e. i am importing TC's to already existing Test Suite. But the problem is i don't see any entries which sholud be altered in the XML file. Could you kindly notify here.

    br
    pradeep

    ReplyDelete
    Replies
    1. GowriNovember 23, 2015 at 2:46 AM

      Looks like few images missing in my blog. I'll try to find thise and update. Meanwhile, can you please send me the first 2 to 3 lines of your xml file?

      Delete
  11. GowriNovember 23, 2015 at 2:51 AM

    The last couple of lines of the testcases xml as well, please :)

    ReplyDelete
  12. AnonymousNovember 23, 2015 at 4:58 AM

    Because of company's confidentiality reason i can't give the XML data with real data. So, i changed the testcase name & providing you with a dummy data which follows the same style like my real data. The first few lines:












    The last few lines are:











    Further Information: I am generating a XML file only with information on Test cases filled in. So in excel sheet, i have not filled Test Suite column, the requirements column and the customs field column for both Test Suites and Test Cases. I hope this is not creating a problem during XML file generation.



    ReplyDelete
    Replies
    1. AnonymousNovember 23, 2015 at 5:02 AM

      hehaaaw!!!!
      i guess ur unable to see the XML code which i copied into the comment. i can't even paste a screenshot here.

      can u mail me to testtools420@gmail.com? From there i can mail u the required information

      pradeep

      FIrst few lines:

      Delete
  13. AnonymousNovember 23, 2015 at 6:07 AM

    Hi Gowri,

    i am back again to annoy you!!
    So, i was going throught the generated XML file. i see some words with "Umlauts (i.e. special German characters like ä, ö , ü and ß)" are converted to "xFC (or) xE4, etc..".

    Question: Are these "Umlauts" not supported in the excel macro? This is for me to make sure that i can find an alternative to enter these splecial chahracters as i am working with German language

    ReplyDelete
  14. JuanmaMay 24, 2017 at 4:05 AM

    Perfect! Thanks

    ReplyDelete
  15. UnknownMarch 16, 2021 at 5:02 AM

    us import and export data Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our.

    ReplyDelete

Subscribe to: Post Comments (Atom)