CODEWITHSUNDEEP
testinghp-quality-center
user559142
user559142

Reputation: 12527

Exporting HP QC defect data to Excel

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...

Upvotes: 6

Views: 40705

Answers (7)

Matthias dirickx
Matthias dirickx

Reputation: 141

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.

Upvotes: 1

Kazi Emran
Kazi Emran

Reputation: 11

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

Upvotes: 1

Arya
Arya

Reputation: 11

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

Upvotes: 1

user4544941
user4544941

Reputation: 1

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

Upvotes: 0

Reddy
Reddy

Reputation: 1

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

Upvotes: 0

rajaishbt
rajaishbt

Reputation: 310

You can export to excel from QC itself.

Defects => Export => All / Selected.

Upvotes: 3

buxter
buxter

Reputation: 593

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

  1. Filter out all the defects you need. Or you just can highlight those you need.

  2. In the menu bar (as far as I remember it's "Defects" menu item, but I might be wrong) -> there is Export option.

Upvotes: 2

Related Questions