Question

I have 500+ tasks in Excel with these columns:

• Title
• Milestone
• Assigned To
• Area
• Priority
• Tags

What's the simplest way to get these into FB using the API? I'm in a .NET environment, and I suppose I could read in the Excel file, build an XML payload for each row, and then POST it to the API. There's got to be an easier way, though. Any thoughts?

Answer 1

I finally got this to work - hopefully this will be helpful to someone trying to do something similar. I've posted the spreadsheet and supporting files at www.caudillweb.com/downloads/fb.zip.

1. I got the API token by logging in as described on this page by @Michael Pryor. A single token lasted long enough for me to get this done. (ed: API tokens last until you click log off in the web interface.) I added this as a named cell in Excel.
2. I added a UrlEncode function to my Excel workbook

3. I added a "Command" column to the worksheet containing the tasks, with this formula:

   ="https://caudillweb.fogbugz.com/api.asp?token=" & Token & "&cmd=new&sProject=ICM&sFixFor=" & D6 & "&sArea=" & urlencode(C6) & "&hrsCurrEst=" & F6 & "&sTags=" & SUBSTITUTE(G6," ",",") & "&ixPriority=" & E6 & "&sTitle=" & urlencode(H6) & "&sEvent=" & urlencode(I6) & "&sPersonAssignedTo=" & urlencode(B6)

4. I used wget.exe to fire off the URLs - you can download this as a self-contained executable here . You'll need to put this in a system path.

5. I copied and pasted the contents of the Command column into a text file, fb.txt .

6. I then made a batch file, fb.bat, containing the following command:

   wget --spider --no-check-certificate -i "x:\users\herb caudill\desktop\fb.txt"

   This tells wget to hit all the URLs listed in the text file. The --spider switch tells it to ignore the results rather than saving them as a file; the --no-check-certificate switch tells it to ignore SSL certificate issues.

Answer 2

All: I found that the answer above worked perfectly, but wasn't facile enough for my client, so I wrote a VB application that does the trick. Also, I wrote it so that it pulls straight from the Excel spreadsheet, so no export is needed.

http://devshed.us/Products/FogBugzCaseImporter.aspx

(please up vote this answer if you found it useful)

Answer 3

Step 1. Execute this in the browser: http://yourfogbugz/api.asp?cmd=logon&email=blah&password=foo Step 2. You should have an API token now. Take what PJM said and make some Excel macro that builds a url of the form http://yourfogbugz/api.asp?token=blah&cmd=newcase&sTitle=thetitlevaluefromyoursheet&sPersonAssignedTo=thepersonfromyoursheet

You'll need to set up the areas, persons, milestones, etc beforehand.

Answer 4

I had some success using Excel VBA macros to pull Case lists from the API and map the XML responses to tables in a spreadsheet. You could do the same, with a macro that walks through your table of cases.

I took that approach because I had started with some Excel tables like you... but quickly learned to hate Excel VBA. with hindsight, I say "stick with the technology you are familiar with". I'm not a .NET developer, but I expect that for someone experienced in it the approach you suggest will be the easiest.

Answer 5

yup, that would work... export the column of URLs into a newline separated text file and pipe that to a command line http Get utility.

(or, set up an XML map in Excel and write a macro to step through the table. I know you're not going to do this, but for the curious, the method to use is WorkBook.XmlMaps("map-name").Import URL:="see above answer" )

Answer 6

Thank you for this info, it is exactly what I needed. There is one issue I haven't been able to work out yet and wonder if you have any ideas. When I use the API to enter a new bug, I want to send in a new line character in the sEvent arg. I've tried all the new line characters I can think of but none work. I entered a bug directly into FogBugz via the UI and then went into the database to see how it stored the newline characters inside the database and all I see is a strange square character that I can't copy and paste.

Any ideas?

Thanks in advance, great article! Bruno

Answer 7

I just tried using wget, and I don't get any new bugs created.

Fore each URL I get the message:

"HTTP request sent, awaiting response... 200 OK"

but when I check the database no bugs were created. When I test the URLs directly in the browser, they work no problem. I'm not sure what I am missing.

edit

If I remove the --spider argument, it adds the bugs, but ends up ignoring the ixBug param, instead starting with the last bug # specified by that param and increasing by one for each bug in the list.

Answer 8

Instead of using wget, I tried using curl for Windows (x64 even) instead, and I managed to finally get it to work.

I created a text file in the format of:

url = apiUrl1<br />
url = apiUrl2<br />
url = apiUrl3<br />
...

and so on, using the method Herb did for concatenating the URLs (and also adding the "url = " at the beginning of each URL) in Excel. I then copied and pasted the column of URLs into a text file, fb.txt.

Then from command line, I ran curl:

curl -K fb.txt

and it worked perfectly.