Once upon a time, an awesome customer put together this short tutorial on using the XML API to import tasks from Microsoft Excel. The tutorial content has been slightly modified since then, but the general steps are the same.

For example, let’s say you have 500+ tasks in Excel with these columns:

  • Project
  • Area
  • Title
  • Milestone
  • Assigned To
  • Priority
  • Tags
  • Case Comment

columnstoimportfromexcel

Download the example Excel workbook with UrlEncode function.

Here’s one way to get these into FogBugz using the API:

  1. Get an API token. (API tokens last until you click log off in the web interface.)
  2. Add a UrlEncode function to your Excel workbook
    1. See how to add a module, or custom function, to Excel
  3. Add a “Command” column to the worksheet containing the tasks, with this formula:
    ="[YOUR FOGBUGZ URL]/api.asp?token="
     & "<INSERT_TOKEN>" &
     "&cmd=new" &
     "&sProject=" & urlencode(A2) & 
     "&sArea=" & urlencode(B2) &
     "&sTitle=" & urlencode(C2) &
     "&sFixFor=" & urlencode(D2) &
     "&sPersonAssignedTo=" & urlencode(E2) & 
     "&ixPriority=" & F2 &
     "&sTags=" & SUBSTITUTE(G2," ",",") &
     "&sEvent=" & urlencode(H2)

    NOTE: Replace <INSERT_TOKEN> below with the value from step (1).  You can also add additional fields supported by the API, including custom fields by adding columns and the appropriate parameter to the formula.

  4. Ensure that wget is installed. You can download this as a self-contained executable here. You’ll need to put this in a system path.
  5. Copy and paste the contents of the Command column from your excel spreadsheet into a text file, fb.txt so that the contents of fb.txt contains all the URLs from the command column of your spreadsheet, like https://[your site].fogbugz.com/api.asp?token=xxxxx&cmd=new&sProject=Sample
  6. Make 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.

  7. Once you run the batch file, your cases should get created. For example:
    imported_case_result

Don’t hesitate to contact us if you have any questions!