Exporting HP QC defect data to Excel

Asked 4/6, 2012 at 0:41 Answered 24/5, 2017 at 8:12

At work a colleague copies and pastes defect data from hp qc to Excel and it takes her ages... it drives me mad!

Is there anyway to export data to Excel from hp qc? It's only required to dump lists of defects and their associated fields like Id, date, summary, assigned to etc....

There must be a way to export to Excel...

Alarick answered 4/6, 2012 at 0:41 Comment(1)

No answers have the desired feature? – Ironbark 24/5, 2017 at 7:50

You can export to excel from QC itself.

Defects => Export => All / Selected.

Brussels answered 28/11, 2012 at 6:23 Comment(1)

Most straight-forward answer. – Ironbark 24/5, 2017 at 8:14

Yes, there is definitely a way to export those defects.

Filter out all the defects you need. Or you just can highlight those you need.
In the menu bar (as far as I remember it's "Defects" menu item, but I might be wrong) -> there is Export option.

Ananthous answered 30/7, 2012 at 9:47 Comment(0)

i am not sure if this will be helpful. To download the filtered defects, one can go to Defects-> Analysis( on the top menu)-> Project report-> Report selected -> then select the format that you would need.

For excel extraction, Defects-> Defects ( on the top menu)-> Export

Crisscross answered 14/12, 2015 at 11:23 Comment(0)

I Wrote a code which will connect to ALM 12.53 and export Defect or any other report into an Excel. You need to have Tool=>Reference..OTA COM Type Library check in Excel 2013. I had problem with HTML code so I have added few line at the below to remove HTML Tag from the Excel Fields.

Sub Main()
Const QCADDRESS = "http://xxx:xxx/qcbin"
Const DOMAIN = "xxxx"
Const PROJECT = "xxxx"
Const QCUSR = "xxxx"
Const QCPWD = "xxxx"

Dim QCConnection, com, recset
Dim XLS, Wkb, Wks, i

Set QCConnection = CreateObject("TDApiOle80.TDConnection")

QCConnection.InitConnectionEx QCADDRESS
QCConnection.Login QCUSR, QCPWD
QCConnection.Connect DOMAIN, PROJECT
QCConnection.IgnoreHtmlFormat = True
Set com = QCConnection.Command


com.CommandText = "SELECT BUG.BG_BUG_ID /*Defect.Defect ID*/ as     defectid  , " _
                & "BUG.BG_STATUS /*Defect.State*/ as state ," _
                & "BUG.BG_USER_TEMPLATE_15 /*Defect.Root Cause*/ RootCause, " _
                & "BUG.BG_USER_02 /*Defect.Assigned To*/ as AssignedTo, " _
                & "BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/ as detectiondate, " _
                & "BUG.BG_USER_01 /*Defect.Application Involved*/  as ApplicationInvolved, " _
                & "BUG.BG_SUMMARY /*Defect.Summary*/  as summary   , " _
                & "BUG.BG_DESCRIPTION /*Defect.Description*/ as description, " _
                & "BUG.BG_SEVERITY /*Defect.Severity*/ as  severity , " _
                & "BUG.BG_DETECTED_BY /*Defect.Submitter*/ as submitter , " _
                & "BUG.BG_RESPONSIBLE /*Defect.Assignee*/    as Assignee, " _
                & "BUG.BG_USER_04 /*Defect.Workstream*/   as workstream , " _
                & "BUG.BG_USER_03 /*Defect.Commited Resolution Date*/ as CommitedResolutionDate, " _
                & "BUG.BG_USER_05 /*Defect.Vendor Ticket Number*/   as Vendorticketnumber, " _
                & "BUG.BG_DEV_COMMENTS /*Defect.Comments*/ as comments " _
                & "FROM        BUG /*Defect*/ " _
                & "where    BG_Status = 'Cancelled' " _
                & "order by   BUG.BG_DETECTION_DATE,BUG.BG_USER_TEMPLATE_15"


Set recset = com.Execute

Set XLS = CreateObject("Excel.Application")
XLS.Visible = False
QCConnection.IgnoreHtmlFormat = True
Set Wkb = XLS.Workbooks.Add
Set Wks = Wkb.Worksheets(1)
'Wks.Name "DataFromBugQuery"

i = 1

Wks.Cells(i, 1).Value = "Defect ID"
Wks.Cells(i, 2).Value = "State"
Wks.Cells(i, 3).Value = "Root Cause"
Wks.Cells(i, 4).Value = "Assigned To"
Wks.Cells(i, 5).Value = "Detection Date"
Wks.Cells(i, 6).Value = "Application Involved"
Wks.Cells(i, 7).Value = "Summary"
Wks.Cells(i, 8).Value = "Description"
Wks.Cells(i, 9).Value = "Severity"
Wks.Cells(i, 10).Value = "Submitter"
Wks.Cells(i, 11).Value = "Assignee"
Wks.Cells(i, 12).Value = "Workstream"
Wks.Cells(i, 13).Value = "Commited Resolution Date"
Wks.Cells(i, 14).Value = "Vendor Ticket Number"
Wks.Cells(i, 15).Value = "Comments"

If recset.RecordCount > 0 Then
i = 2
recset.First
Do While Not (recset.EOR)


Wks.Cells(i, 1).Value = recset.FieldValue(0)
Wks.Cells(i, 2).Value = recset.FieldValue(1)
Wks.Cells(i, 3).Value = recset.FieldValue(2)
Wks.Cells(i, 4).Value = recset.FieldValue(3)
Wks.Cells(i, 5).Value = recset.FieldValue(4)
Wks.Cells(i, 6).Value = recset.FieldValue(5)
Wks.Cells(i, 7).Value = recset.FieldValue(6)
Wks.Cells(i, 8).Value = recset.FieldValue(7)
Wks.Cells(i, 9).Value = recset.FieldValue(8)
Wks.Cells(i, 10).Value = recset.FieldValue(9)
Wks.Cells(i, 11).Value = recset.FieldValue(10)
Wks.Cells(i, 12).Value = recset.FieldValue(11)
Wks.Cells(i, 13).Value = recset.FieldValue(12)
Wks.Cells(i, 14).Value = recset.FieldValue(13)
Wks.Cells(i, 15).Value = recset.FieldValue(14)


Dim r As Range
Wks.Cells(i, 8).NumberFormat = "@"  'set cells to text numberformat
Wks.Cells(i, 15).NumberFormat = "@"
With CreateObject("vbscript.regexp")
.Pattern = "<[^>]+>|;"
.Global = True
 For Each r In Wks.Cells(i, 8)
 r.Value = .Replace(r.Value, "")

Next r
For Each r In Wks.Cells(i, 15)
r.Value = .Replace(r.Value, "")
Next r
End With

Text = Wks.Cells(i, 8).Value
Wks.Cells(i, 8).Value = Replace(Text, "&nbsp", "")
Text = Wks.Cells(i, 8).Value
Wks.Cells(i, 8).Value = Replace(Text, "&quot", "'")

Text = Wks.Cells(i, 15).Value
Wks.Cells(i, 15).Value = Replace(Text, "&nbsp", "")
Text = Wks.Cells(i, 15).Value
Wks.Cells(i, 15).Value = Replace(Text, "&ltv6ucbs&gt", "")

i = i + 1
recset.Next
Loop

Wkb.SaveAs "C:\Users\xxxx\Downloads\Files\Cancelled_Defects.xls"
End If

Wkb.Close
XLS.Quit

QCConnection.Disconnect

Set recset = Nothing
Set com = Nothing
Set QCConnection = Nothing
Set XLS = Nothing
Set Wkb = Nothing
Set Wks = Nothing
End Sub

Treasonous answered 19/1, 2017 at 16:7 Comment(0)

All valid options, and I'll add one more: The Excel query option in the analysis menu.

Dashboard > Analysis view > Add button > New excel report

You will have to provide a name for the report, then confirm.

In the Query window you can type a query. How to join the tables? Consult the database scheme at help > documentation library > HP ALM Project Database Reference

You can add Post-processing to structure the excel.

Each query added, will land in a new Tab in excel.

This way you can compose quite complex excel reports.

Take note though: When adding post-processing, you create a xlsm file and you need to enable macros.

Ironbark answered 24/5, 2017 at 8:12 Comment(0)

You can filter and select the defects click on the Defects --> Export --> either ALL or Selected and save the file.

I have one more question here like ..is there any way to save the file other than XLS format to save in CSV or XML format. Can some one guide me ..Thanks

Conclusive answered 13/5, 2014 at 15:21 Comment(0)

1.Log in to HP QC

2.Go to defects (left menu)

3.Filter your defects

4.Go to defect menu in Top row

5.Click and select Export button

Dupuis answered 9/2, 2015 at 5:16 Comment(0)

Recommended topics

Hot tags