• Blog
  • Issues
  • Can you import Jira tasks from Excel? Here is how.
jira-issues-import-excel-illustration
Author: Bogdan Gorka
Published: January 11, 2021
CategoryIssues

Importing data into Jira is one of the elementary administration skills. There are often cases when you need to import data for your new project from Excel or when you want to copy data between Jira Server/Data Center and Jira Cloud. The easiest and most common way to migrate data to and from Jira is using CSV files, however there are often problems with this method. In this article I will discuss two popular scenarios and will show you some special tips that can save you a few hours.

How to prepare an Excel file to import Jira issues

The first use case is a simple import of Jira issues that were created in Excel. It is often required, when some works or tasks were planned in Excel and now they need to be imported to Jira.

In order to import your tasks into Jira, you need to first have a proper Excel file format. See an example below.

jira-issues-import-excel-01

The Excel file needs to be properly formatted for importing data into Jira. There are a couple of elements that I always insert into my Excel file before the import into Jira:

  1. Column header names in the first row - each column needs to have a header to be mapped during the import
  2. Issue type values depend on the project configuration. In this use case I will use initially Epics and Tasks. If you want to follow this example in your Jira project, make sure that your issue scheme has these issue types.
  3. Values in the Epic Link column needs to be exactly the same as in the Epic Name column. Otherwise the tasks will not be linked with Epics. Remember to have the Epic link field on the Task (or Story) issue screen.
  4. The Summary column contains the title of your issue/task in Jira
  5. Epic color (optional) - it is a good idea to give epics some colors, otherwise they will all be gray and it will not look nice on the scrum board. Use values from ‘ghx-label-1’ to ‘ghx-label-9’. Also Jira Cloud offers nice Epic link visualisation on the issue screen.
  6. Priority column - you should import your priority column, otherwise this field will be empty on the issue screen. Before importing data into this column, check your Priority names.
  7. Description field is optional but it is good to insert some descriptions for more complex tasks. For example, the Definition of Done criteria.

You can download example Excel files from this GoogleDrive location.

Watch how to import Jira tasks from Excel file

If importing Jira tasks from Excel is a completely new topic for you, watch this video tutorial on my YouTube channel where I explain a basic import scenario.

How to prepare and import the CSV file

Once you have a properly configured file, save it as CSV in Excel. Make sure to use the proper character coding UTF-8. This is important to select UTF-8 character coding to avoid coding problems later, especially when you use data written in other languages than English.

jira-issues-import-excel-03

CSV file troubleshooting

Below is a CSV file opened in Notepad++. This file will cause problems because after the last row it  it contains comma characters. Jira will see it as empty rows and will try to import it.

Sometimes CSV file is exported from Excel with empty lines. You can spot them when you see many commas at the bottom of your CSV file. 

jira-issues-import-excel-07

Before using the exported CSV file it is good to open this file in Notepad. Always delete these lines if you do not want to see the error screen “Error importing issue [externalId='autoid-657502936120688329', summary='null'“. See the example below.

jira-issues-import-excel-08

Import the CSV file with new Jira issues

Now go to Jira Administration / System tab / External system import and click the CSV button

jira-issues-import-excel-04

In the next step do the field mapping. This process is simple and straightforward. 

In case of trouble, click the check boxes in the Map field value. Then you will see how Jira maps the values. 

jira-issues-import-excel-05

For example, in sections where are drop-down lists, I make sure the mapping is correct and I never leave the option ‘Map as is’ (see the screen below)

jira-issues-import-excel-09.png

When you are ready, press the button ‘Begin import’ and let the data flow into your Jira.

jira-issues-import-excel-06

When everything goes well, you should see a similar screen as below.

jira-issues-import-excel-10

How to import Epics, Tasks and sub-tasks to Jira in one data import?

If you want to import the whole hierarchy of Jira issues i.e. Epic > Task > Sub-task, you need to use special columns Issue ID and Parent ID.

Before doing so, it is always good to check if your Jira already contains Issue ID numbers in the ‘External issue ID’ column. This column is visible only in Jira Server search and not in Jira Cloud. I did not test if Jira Cloud stores External ID numbers in some other way.

In Jira Server, go to menu Issues/Search for issues and use this JQL query:

"External issue ID" is not EMPTY.

If this query results is null, you can use any unique and sequential number in the Issue ID column of your CSV file. But if this column is not empty, unhide the column “External issue ID” and see the existing numbers. In your CSV file you will need to use numbers from a different range.

In my example below, I discovered numbers from 5000 which I used in my previous CSV import. 

jira-issues-import-excel-02

That is why in my next import I will use numbers from 6000 onwards. Finally, I will delete them all in one go later with the bulk edit operation.

Below is the example how the Excel file should look like if you want to import Epics, Tasks and Sub-tasks in the same import. I tested this file in both Server and Cloud Jira versions.

jira-issues-import-excel-11

You can download example Excel files from this GoogleDrive location.

Tip 1: One comma can cause you a headache

As you know by know, you need to separate your column data in one row with a special character. By default this character is a ‘comma’ 

jira-issues-import-excel-12

However, very often description fields or custom fields contain commas in sentences. If you do not check it, this will ruin your issues import. That is why my recommendation is to check your data in Excel first, if commas are used anywhere. If yes, you can either delete them or change the list separator setting. This setting is used by Excel to create CSV files.

If you use Windows, you can change it in the Regional settings. This change does not have to be permanent but only for the duration of your Jira issues migration.

jira-issues-import-excel-13

Tip 2. Carriage return for multi-line descriptions

In one of my projects I needed to have more advanced description fields in my issues. The requirement was to create a multi-line description. The lines of this description were coming from a few columns in Excel. I needed to collect the content of these columns and display them as one multi-line content in Jira.

 See the Description field below.

jira-issues-import-excel-14

If you want to achieve that result, you need to join columns with an additional character ‘\\’.

In my example, I also made the column title bold using the asterisk sign '*'. See the example screen shot below of the Excel file.

jira-issues-import-excel-15

Before you go

All I can say about data migration in Jira is that every time it causes some surprises. In this article I have given you a solution to a couple of them.

When you do your data migration to Jira, always migrate them to your testing system first. Especially if you have a few thousand issues to be imported, it is better to have errors or tasks not linked with Epics on the testing system, where you can delete all your imported issues and start again.

Make sure that you do not have any notification scheme in the project where you practice your Jira imports, especially if you are importing issues with Assignees. Notifications schemes often send notifications to Assignees when they are assigned to new tasks.

In the production environment it is a good idea to check the notification scheme first and in case of need, to turn the notifications off during the import. 


Spread the knowledge, share with others

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Teach Yourself Jira Ebook Cover

Get free e-book

Teach Yourself Jira Administration

Join our ever growing group of Jira Administration enthusiasts and get this e-book for free.

Teach Yourself Jira Administration is my selection of best Jira Administration courses for Jira - over 30 hours of recordings.

In this e-book I selected only the few recommended courses with good and effective content for which you do not have to pay (if you follow my instructions).