It's all about the answers!

Ask a question

How to programatically run a query and get results in CSV form?


Steve Francisco (26139) | asked Nov 21 '14, 1:55 p.m.
edited Nov 21 '14, 1:56 p.m.
 I've got a shared query that I'd like to export as CSV so another tool can consume the data.  Currently I manually use the RTC client, right click on the query and choose "Export work items", but I'd like to automate this.

I've been playing with the Plain Java API, and am able to find the shared query and even get the IQueryResult object back, but it gets murky after that.  From what I can tell, I'm supposed to iterate over the result, resolve them into IWorkItem objects, then go about collecting the information I'm interested in.  I presume from there I can format it as CSV myself.

Since the shared query already specifies the columns I'm interested in, shouldn't the IQueryResult have a way for me to get at those values directly without accessing the full WorkItem for each result?  

What I'd really like is an equivalent to "Export Work Items" that I can call from a script or program.

3 answers



permanent link
sam detweiler (12.4k6173200) | answered Nov 21 '14, 2:53 p.m.
see my utility here which executes the query and processes all the results (accepted answer)

https://jazz.net/forum/questions/94776/assertionfailedexception-problem-with-getting-the-values-of-attributes

I don't know of another reliable way

permanent link
Karthik Krishnan (859391133) | answered Nov 21 '14, 4:06 p.m.
edited Nov 24 '14, 5:08 a.m.
 Well I just finished developing an application for this :-) I can post the code Monday as I don't have access to the code now
But you can look into this for some pointers: 

https://jazz.net/forum/questions/144358/query-export-to-csv-looking-for-url?page=1&focusedAnswerId=144361

What you need is the query URL and code to login to RTC and then download the CSV
-----------------------------------------------------------------------

If you simply want to export a predefined query in to a CSV file, you may use the following url and automate this

https://<server>:9443/ccm//resource/itemOid/com.ibm.team.workitem.query.QueryDescriptor/<UUID_of_query>?_mediaType=text/csv
	

This would return the query result in CSV format. You may use any programming language to use this URL to schedule the CSV download  process

What I have done so far is that I have Java application which will login to RTC and then execute this URL and download the CSV File
Note: The output of CSV is same as "Download CSV" from the query results

You may also user CURL to do this job

I found this easier than using the IQueryClient & IQueryResult




Comments
Pavithra Kasturirangan commented Jan 13 '17, 4:40 a.m.

 In version 5.0.2, when i try the format curl -k -b $COOKIES https://rtcURL:9080/jazz/resource/itemOid/com.ibm.team.workitem.query.QueryDescriptor/_CcUREJTLEeW6Fe3HWhxOig?_mediaType=csv  i get a csv created but its contents are not query output. They look like the query definition itself.

Could you please confirm if the above format is right....


permanent link
Ralph Schoon (55.8k23642) | answered Jan 13 '17, 5:03 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

I added something to the work item commandline a while ago: https://rsjazz.wordpress.com/2016/10/25/the-rtc-work-item-command-line-on-bluemix/

Your answer


Register or to post your answer.