Export Test Cases from Quality Center (QC) to excel-with design steps

I have been looking for this since very long. I thought this cannot be done till i learned excel reports in quality center.

So, follow the following steps to export test cases along with design steps from quality center to excel sheet. You must be added as an administrator to the project in order to perform below steps:-

1. Identify the Test ID’s of test cases you want to export. Normally, it would be in sequence like from – 79 to 80.

2. Go to Tools –> Excel Reports

2. Under Public folder, click ‘New Excel Report’ icon on top left corner.

3. A dialog box asking for report name would populate. Type in the report Name and click Ok.

4.Type some description about your report.

5. Click on Query Tab.

6. Copy and paste the below query in sheet1.
SELECT TS_TEST_ID,TS_NAME,TS_DESCRIPTION,DS_STEP_ORDER, DS_DESCRIPTION, DS_EXPECTED, DS_STEP_NAME

FROM TEST,DESSTEPS
where TEST.TS_TEST_ID = DESSTEPS.DS_TEST_ID
and TEST.TS_TEST_ID between 79 and 305
ORDER BY TS_TEST_ID, DS_STEP_ORDER

7. Click on the ‘Test Query’ button  to test the query. This would compile the query and report errors if any. Also, It would provide a snapshot of the results.

We normally name out test cases in specific formats and they normally contain the test set folder or project name in them.

so below query may be used as an alternative:-


SELECT TS_TEST_ID,TS_NAME,TS_DESCRIPTION,DS_STEP_ORDER, DS_DESCRIPTION, DS_EXPECTED, DS_STEP_NAME

FROM TEST,DESSTEPS

where TEST.TS_TEST_ID = DESSTEPS.DS_TEST_ID

and TEST.TS_NAME LIKE ‘%XYZ%’

ORDER BY TS_TEST_ID, DS_STEP_ORDER 


8. Click on ‘General Setting’ tab and type the path where the excel file needs to be exported in the text box.

9. Again come back to the query tab and click on ‘Run Query’.

10. Click on ‘Generate Excel Report’ icon on top left corner.

11. Following message would be displayed. Click Yes.

12. Again, the ‘Save report’ window would come. Specify the path of report and click ‘Save’.

13. The report generation may take some time depending on the amount of data.

14. Once generated, the excel sheet would open up itself. If not opened, navigate to the path specified.

54 thoughts on “Export Test Cases from Quality Center (QC) to excel-with design steps”

  1. Thanks for the post… However I'm having some difficulties exporting to Excel. I'm using QC10.0 I have also install the add-ins for Excel but I don't see the Excep Reports optin available under Tools. Any Suggestions will greatly appreciated!!

  2. Hi Amita,
    Great post. This is first time i see that somebody took this much effort to post with screen shots. I am learning QC and this post really helped a lot.

    Thanks,
    Radhika

  3. Hi Murali,

    Though i am yet to upgrade my QC version, here are few steps i have managed to find out via my friends:-

    1. dashboard module->Menu Analisys–> New Excel Report:

    Or below steps may help:-
    To Create Excel reports -We need to have access for DashBoard module in QC 10.0
    For QC 9.2 the reports option is under Tools – Home Page
    and For QC 9.0 there is no option to generate Excel Reports

    Navigation – QC 10.0 –
    1) Go to DashBoard module
    2) Select Excel Report in top left corner
    3) Enter Report Name
    4) Write Query under Query Tab
    5) Add macro under Post Processing Script
    6) Set the Status to "Ready"
    7) For test purpose we can use Query Generator
    8) For viewing of Tables we can use Query Generator
    9) Click Generate Query
    10)There you go report

  4. hi i am using QC 10.0 and i am not much familiar with queries. i want a better way for exporting test case with steps from Qc to Excel. So can you help me regarding this…. i am waiting for your response…

  5. Hi Ashok,

    I am sorry to say that query is the only option if you want to export text cases in bulk from QC to excel sheets.

    However, if you have patience and time, you may export them one by one by navigating to Test Lab tab. Go to the test steps of test case to be exported, right click on the steps, click export all, and follow the steps on screen.

  6. can u please help me with the QTP scirpt to convert QC test results into html file..in qc we have an option to lunch report(result viewer in qtp).the report is in the tree format.. but i want the result in the format of HTML table or excel format

  7. Qwesome thanks this was very helpful but can i get get the testcases based on the filter criteria of test set folder

    in that case we will not have the sequential test case numbers

    if you provide the query it will be very helpful

  8. Hi Venki,
    I could not find a way to export based on filter criteria set on test set folder, but have found a other way round which may sound much easier.
    We normally name out test cases in specific formats and they normally contain the test set folder or project name in them.
    so below query may help:-

    SELECT TS_TEST_ID,TS_NAME,TS_DESCRIPTION,DS_STEP_ORDER, DS_DESCRIPTION, DS_EXPECTED, DS_STEP_NAME
    FROM TEST,DESSTEPS
    where TEST.TS_TEST_ID = DESSTEPS.DS_TEST_ID
    and TEST.TS_NAME LIKE '%XYZ%'
    ORDER BY TS_TEST_ID, DS_STEP_ORDER

    I would come up with a new post on this soon… 🙂

  9. Hi,

    I am using QC v10.0 and I do not see the New Excel report option under Analysis menu. Is there a prerequisite needed to be installed? I have installed the Excel add in for importing from excel to QC.

    Thanks,
    Heba

  10. Hi Heba,
    i think excel report comes in Analysis menu in Dashboad module.
    Also make sure you have permissions to work with report generator.
    -Amita

  11. Good Post. Is there a way to include one row for any test case that does not have any steps ? The original query on this page only includes test cases that have at lease one step

    Barry

  12. Hi Barry,
    Each test case should have at-least 1 step in it. There is no use of creating a test case in quality center if it does not have any test step.

  13. Hi Amita,
    This post was indeed helpful. Would like to appreciate your taking time out and doing something like this to help others.
    Keep up the good work!

    Cheers

  14. Very good post Amita. Thanks very much. This will save us a lot of time, as we are having to copy some tests written in QC v10 back to QC v9.2.
    Well done
    Chris

  15. Hi Amita,

    Any idea about issues of exporting to excel from QC 11 ALM? for some reason I cannot export a group of defects I have filtered in QC to excel 2010. Have you heard of any support issues here?

    Very specific question, but it'd be great if you have any suggestions..

    Thanks,
    Sam

  16. Hi Sam,
    I have not used ALM yet… We are i process of upgradation… .Si, i am very sorry, i am not able to help you on this as of now.

    You may try posting a query on HP's discussion forum. I have been getting good response from them for some of my issues….. But you need to follow them up…

  17. Great post! That is what I was looking for! The one really NEEDDED thing: can we export along with test cases – attachments? we are moving our test cases from QC and attachments are really usefull things for our test cases? – any thoughts? please contact me if U have an answer for that : igor.tyshchenko@gmail.com OR Skype: igor.tyschenko

  18. Hi,
    For attachments also, there must be some tab;e storing their locations. Try to find it out.

    I am sorry i can't dig into that as i am on extended leaves and cannot access QC for some more days….

  19. I thought that TS_ATTACHMENT or ST_ATTACHMENT – are my fields but all they can show me just 'Y' in result column.
    Cannot find field with path =

  20. Is there any way that you can export test cases from HP QC 11 to a Word doc from Test Plan ?and have a specific format for the same ???Reallly looking at theat point !!!

    Suresh

  21. The above query is working for manual test scripts .. i am trying to import BPT scripts in excel sheet ..
    can anyone provide query

  22. Hi,
    While exporting the defects from QC I am getting the QC id. Is there a way to convert those IDs into Names -Full names. Kindly provide the solution

    Thanks & Regards
    AR

  23. Hey Amita..

    Really a great work. Your post really helped me a lot.
    I have one query. I want to add one more parameter while exporting the Test Cases to excel and that is Priority of the Test Cases. Can you please help me with the changes in the script needed to fetch priority as well with the other details.

    Really appreciate your support.

    Thanks,
    Amit

  24. Hi Asan/Amit,
    It seems that Test Priority is not the default fiend of quality center. IT may have been added by your administrator. You need to find out the table which contains this field. It should be either TEST or DSSTEPS table.

    The table fields may be found by navigating to the Query tab in excel reports tools.
    This tab contains all the tables on right hand side. expand the required table to see it's columns.

    -Regards,
    Amita

  25. Hi

    This is really very helpful.
    iam using QC10.I have testcases calling another testcases.
    Then how can i get all steps from thoe testcases to excel.
    Thanks in advance.

  26. Hi

    I am using QC 11.0 ALM, but i am not able to get Excel Report Generator neither in Tools menu nor in Dashboard view.

    Can anyone help me on this as i need to export test cases from QC, also as the test cases are on Client's VM so i cannot install any addin for exporting.

    Thanks in advance
    Ravinder

  27. Hi Ravinder,
    Although i have not used ALM, but looking in ALM help, i think it is found at –
    DashBoard –> Analysis View –> Public –> Excel Reports.

  28. Amita,

    Thanks for the post.
    There are plenty of post out there but this one is the best.

    Appreciate your efforts.

    Best Regards

  29. Hi Amita,

    The above query is just to export a single test case with steps. I want the Query to export all the test cases with steps.If you have this, Please share it with me.

  30. Exporting bulk test cases (with steps) from ALM (QC11.52)

    1. Go to Test Lab and create a ‘unique test folder’.
    2. Now create a ‘Test Set’ under that folder and add required test cases (which are going to export on to desktop) in this.
    3. Go to ‘Analysis View’ and create an excel report called ‘Test ID’ under Private or Public folder as shown in below image.
    4. Select the report ‘Test ID’ and go to configuration Tab. Then select the query Tab under Configuration Tab as shown in below image.
    5. Now paste the below code in the query1 field and click on generate icon.

    select CYCL_FOLD.CF_ITEM_ID,CYCL_FOLD.CF_ITEM_NAME, CYCL_FOLD.CF_FATHER_ID as ParentId from CYCL_FOLD where CYCL_FOLD.CF_ITEM_NAME = '@@@@'

    Note: Mention the Test Folder Name in the place of @@@@
    6. You will get the below pop-up and click on ‘Yes’.
    7. Save the report on desktop and open it.
    8. Open the file on desktop and note down the CF_ITEM_ID from file.
    9. Again go to Analysis View and create one more excel report and name it as ‘GetTestcases’.
    10. Select the report ‘ GetTestcases’. Go to ‘Query Tab under configuration Tab and Paste the below code in the Query1 field.
    SELECT TEST.TS_TEST_ID as TestID, TEST.TS_NAME as TestCaseName, TEST.TS_DESCRIPTION as Description, A.StepName,A.StepDescription,A.ExpectedResult from TEST inner join
    (Select DESSTEPS.DS_TEST_ID as TestId, DESSTEPS.DS_STEP_NAME as StepName, DESSTEPS.DS_DESCRIPTION as StepDescription, DESSTEPS.DS_EXPECTED as ExpectedResult from DESSTEPS where DESSTEPS.DS_TEST_ID in ( SELECT TEST.TS_TEST_ID FROM TEST, TESTCYCL, CYCLE, CYCL_FOLD where TEST.TS_TEST_ID= TESTCYCL.TC_TEST_ID and CYCLE.CY_CYCLE_ID= TESTCYCL.TC_CYCLE_ID and CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID and CYCL_FOLD.CF_ITEM_ID in (####)))A on A.TestID= TEST.TS_TEST_ID
    11. Now Paste the CF_ITEM_ID in the place of ‘####’(which is mentioned in the above code).
    12. Now click on ‘Generate’ icon and save the file on desktop.
    13. Open the file and you can align the test cases as you wish.

  31. Hi Chinni I'm try to export test cases/steps per your instruction above but got as far as step 4. Under configuration tab, I do not see 'Query Tab' is this to do with type of access? Or are you referring to 'Filter'? Please advise at your ealiest, thanks.

Comments are closed.