Export HP QC defect data to Excel

Question

At work, a colleague copies and pastes defect data from hp qc to excel, and it takes her age ... it drives me crazy!

Anyway, to export data to excel from hp qc? This is only necessary to reset defect lists and related fields, such as Id, date, summary, assigned, etc.

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

Answer 1

You can export to excel from QC itself.

Defects => Export => All / Selected.

Answer 2

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

• Filter out all the defects you need. Or you can simply select the ones you need.

• In the menu bar (as far as I remember this menu item "Defects", but I can be wrong) → there is an export option.

Answer 3

I am not sure if this will be useful. To load filtered defects, go to the "Defects-> Analysis" section (in the top menu) → Project Report-> Report selected → then select the format you need.

To extract excel, Defects-> Defects (in the top menu) → Export

Answer 4

I wrote code that will connect to ALM 12.53, as well as an export defect or any other report in Excel. You need to have Tool => Reference..OTA COM Type Library check in Excel 2013. I had a problem with the HTML code, so I added a few lines below to remove the 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 

Answer 5

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

I have one more question, for example: is there a way to save a file other than the XLS format for saving in CSV or XML format. Can anyone help me .. Thanks

Answer 6

1. Log in to HP QC

2.Go to defects (left menu)

3. Filter your flaws

4. Go to the defects menu on the top line

5. Press and select the Export button.

Answer 7

All valid parameters, and I will add one more: Excel query option in the analysis menu.

Dashboard> Analysis view> Add button> New Excel report

You will need to indicate the name of the report and then confirm.

In the query window, you can enter a query. How to join tables? Refer to the database schema in the Help> Documentation Library> HP ALM Project Reference Database

You can add post processing to structure excel.

Each added query will add a new tab to excel.

This way you can compile quite complex Excel reports.

Please note: when you add post processing, you create an xlsm file and you need to enable macros.