JIRA Import / Export via Excel

This article describes how to export from CaseComplete into Jira, and back using Excel files. Starting in version 2015, you can export directly to Jira without the need of an Excel file. However, if you want to import from Jira, you'll still need to use the techniques described in this article.

Decide what to export

You can export almost any kind of CaseComplete item to Jira, for example, use cases, requirements, or issues. In this example, we'll export Requirements that have their Type field set to Task or Story. These are two custom types added just for this example via Tools / Built-in-fields - you'll likely have a different subset of items to export. So that you can quickly find these requirements, consider creating a filter:

Create a custom field to track the Jira ID

If this is a one-time export, you can skip this step. However, if you want to change items in CaseComplete and export again to update the corresponding Jira items, you'll need to track the Jira ID in CaseComplete. Do this by creating a custom field (you can name it anything). We'll show how to fill in the values for this field in a later step.

Create a custom Excel report

Decide which fields you want represented in Jira and create a custom Excel report that has those fields. An easy way to create a custom report is to start with one of the built-in reports, such as Requirements List, and remove fields that you don't need, and add any custom fields you would like exported. In this example, we've added a custom field for the Jira ID. If you'd like, you can download the example shown here at the end of this article.

The first row contains the name of each field. The name isn't important, but you might want to use Jira terminology to help keep things straight when you perform the import. The second row contains the reporting keywords that CaseComplete uses to fill in the data.

Run the report and save it as CSV

If you're using a filter as we showed in step 1 above, you can click the Report button found in the header area of the filter tab. Otherwise, generate a report as you normally would. In the generated Excel report, perform a File / Save As and choose CSV (Comma delimited) (*.csv) in the Save as type dropdown list. You'll get one or two warnings about file compatibility; just click Yes indicating that you want to save anyway.

Create a Jira custom field to track the CaseComplete ID

By saving CaseComplete IDs in Jira, you'll be able to push changes made in Jira back to CaseComplete. As before, if this is just a one-time import, you can skip this step. To keep this article shorter I'll defer to Jira's documentation on how to create a custom field. As before, the name of the custom field is up to you.

Import into Jira

To import in Jira, first navigate to Administration / System:

Then select External System Import in the side menu:

From there, select Import from CSV and upload the CSV file that you saved earlier. You'll be asked to map the fields found in the CSV file to corresponding fields in Jira. This is mostly straightforward, however there are a few things to keep in mind:

• Map the Jira ID field (the custom field we created in CaseComplete) to the Issue Key field in Jira.
• Map the Name field in CaseComplete to the Summary field in Jira.
• Select the map field value checkbox if you need to map the values of CaseComplete fields to corresponding values in Jira. A good example is priority, where CaseComplete uses integer values, and Jira uses text values. In this example, we've mapped CaseComplete priority 2 to "should" in Jira:

You're now ready to import. After the import completes, you'll be given the opportunity to "save the configuration for future reuse". If you select this, it will save you from having to map the fields again in subsequent imports.

Export the Jira IDs to CaseComplete

If you created a Jira ID custom field in CaseComplete in step 2 above, you can now fill in values for that field. We'll do this by exporting an Excel file from Jira and importing that into CaseComplete. First, navigate to the Issues page in Jira. If you have issues in the project other than those you just imported, you can filter the list so only the CaseComplete items are shown:

Now select Export / Excel (Current Fields) from the Export menu in the upper right:

This will create an Excel file that contains the fields that are currently showing in the Issues view. Since we're only going to import the Jira ID (aka the Issue Key), the fields that are currently being displayed isn't important. However, keep in mind that you can use this same technique if you make updates in Jira and want those changes reflected in CaseComplete. In that case, you'd want to customize the view to make sure that the relevant columns are showing.

Open the Excel file that gets created. You'll get a warning, but just ignore it and open the file. The reason you get the warning is because in fact, it's not an Excel file, rather it's an HTML file with an xls file extension. It just happens that Excel is smart enough to load the HTML and turn it into a spreadsheet.

You'll need to do some editing in order for CaseComplete to be able to import the file. First, delete the Jira logo, the first 3 rows, and the final row:

Then delete all the columns except for two: the CC ID column (or whatever name you chose for the Jira custom field) and the Key column. Remember, all we're doing in this step is importing the Jira ID into the corresponding requirement in CaseComplete, so those are the only 2 fields we need. Finally, rename the column headers to match the field names that CaseComplete expects. In this case, use Jira ID (or whatever name you chose for your CaseComplete custom field) and ID. You should end up with something like this:

Perform a File / Save As, and set the save as type be a regular Excel Workbook (*.xlsx). You can now import the file into your CaseComplete project via the Connect tab (or File / Import/Export if you're using an older version). Inspect the Jira ID custom fields to make sure they imported correctly. Now, the next time you generate the Excel report for export, the Jira ID column will have values filled in. Jira will use these values when importing to update the existing items instead of creating new ones.

Summary

This article is long with a lot of steps so it may seem daunting, but (hopefully) it's not as bad as it looks. Still, you should experiment on a test project before updating a production project just to get all the kinks worked out and make sure the data transfers as expected.

If you end up using this technique to export to Jira, please consider adding a comment here or sending us an email to tell us more about your integration. For example, which CaseComplete items did you export? How did you map them into Jira issues? We'd love to hear about it because it will help us as we consider developing a tighter integration with Jira in the future.