It's all about the answers!

Ask a question

I want to import from another system via CSV, but then do updates. How do I do this?


Daniel Toczala (1113) | asked Oct 06 '15, 4:21 p.m.
I want to import from another system via CSV, but then do updates (say once a week) through additional CSV imports.  So I would do a second CSV import, and select "Update Matched Workitem", but then how do I match the work items?  The second time through, the import file has an external ID (which I save in the work item on initial import), but it has no idea of the RTC ID to map this to.

Is doing something like this (a weekly import/update of work items from an external source) possible?

3 answers



permanent link
sam detweiler (12.5k6189201) | answered Oct 06 '15, 5:15 p.m.
When we needed to do this, we added a field to the workitems that would hold  the external system ID.

then we wrote an RTC query to get all the workitems where the external ID was not empty and the RTC ID.
(so we could tell imported workitems from new)

then sorted the update file with the external ID and  merged the RTC ID into each row.

then did another import with update.
we ended up writing a little utility to do the sort & merge


Comments
sam detweiler commented Oct 06 '15, 5:20 p.m.

I can't believe you asked this and I answered!


Daniel Toczala commented Oct 07 '15, 9:43 a.m.

Hey, I thought that I knew how to do this, and when I tried it quickly I soon found that the answer that I came up with was the answer that you provided.  It's not a pretty alternative - although it will give ma an excuse to do some quick scripting work.

I figured that somebody out there has to have developed a simple tool for doing this "CSV merge", or that maybe there was something new in V5 or V6 that dealt with this.


permanent link
Krzysztof Kaźmierczyk (7.4k35499) | answered Oct 07 '15, 6:51 a.m.
Hi Daniel,
I had already the same issue in the past. Look at this: https://jazz.net/forum/questions/130299/how-to-preserve-workitem-external-id-when-importing-from-csv

Comments
Daniel Toczala commented Oct 07 '15, 9:48 a.m.

This is the same approach that Sam detailed above.  I hate having to do the spreadsheet merge - it's too error prone and labor intensive.

Has anyone written a utility or a script that will take a CSV and a query as input, and merge the two into a CSV file ready for an update import?  It would be great to have a cron job that runs once a day/week that would just go out and process updates to RTC from a remote system.


sam detweiler commented Oct 07 '15, 11:45 a.m.

i tried to run the curl extract of a query CSV download from the web UI link like we could do a couple releases back.. now get 'no javascript' error.

then a small java app to merge the two files, based on old id
have to decide what to do about missing/mismatches.

then run import/update from the new file.

shame to have to build your own export tho.


permanent link
sam detweiler (12.5k6189201) | answered Oct 07 '15, 12:48 p.m.
edited Oct 07 '15, 12:53 p.m.
rediscovered the csv extract url, from

https://jazz.net/forum/questions/131393/how-to-display-a-formatted-query-result-in-a-rtc-dashboard-external-content-widget#131413

little windows batch script

set COOKIES=.\cookies.txt

set USER=select userid
set PASSWORD= make pwd
set HOST=rtc repo url & port (https://server:port)
set QUERY_UUID=_GjVlsE-mEeSfnK2WDtTMxQ

get from web view of download query to csv

curl -k -c %cookies% "%host%/jts/authenticated/identity" >nul

curl -k -L -b %COOKIES% -c %COOKIES% -d j_username=%USER% -d j_password=%PASSWORD% %host%/jts/authenticated/j_security_check >nul

curl -k -L -b %COOKIES% -H "Accept: application/xml" "%host%/ccm/resource/itemOid/com.ibm.team.workitem.query.QueryDescriptor/%QUERY_UUID%?_mediaType=text/csv"


Your answer


Register or to post your answer.