How to programatically run a query and get results in CSV form?
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.
What I'd really like is an equivalent to "Export Work Items" that I can call from a script or program.
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
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
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
-----------------------------------------------------------------------
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
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....
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/