Purpose

As Jira Cloud applications do not support project imports, customers rely highly on the CSV Import options to provide this functionality. This documentation is targeted to provide some assistance to help export data from a Jira Server application database and to import it to a Cloud application. 

 The KB is written on the query tested on Postgres, as such the syntax will need to change based on the Database type that you are using. If you have questions, do not hesitate to post it on Atlassian Answers where our community resides.

Solution

Step 1 : Exporting Issues

1. Execute the below query to export issues, replacing <Enter Project Key Here> with the project you wanted to export. EG: JSP, JRA, TEST and etc. Additionally, replace <Enter Location to Save File> to the location desired for storing the CSV file generated. (Note: The user running Postgres must have permission to create file on the folder specified).

   copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, i.reporter, i.assignee, t.pname as issuetype, to_char(i.created,'dd/MM/yy HH24:MI') as created, to_char(i.updated,'dd/MM/yy HH24:MI') as modified, i.description, to_char(i.duedate,'dd/MM/yy HH24:MI') as duedate, i.environment, s.pname as priority, i.resolution, i.resolutiondate, w.status, i.timeestimate as originalestimate, i.timeestimate as remainingestimate, i.timespent
   from jiraissue i, project p, issuetype t, os_currentstep w, priority s
   where i.project = p.id 
   and i.issuetype = t.id
   and i.workflow_id = w.entry_id
   and i.priority = s.id
   and p.pkey = '<Enter Project Key Here>')
   to '<Enter Location to Save File>' with CSV HEADER;

   Edit: For those of you who are trying to execute this in a MySQL database, we've taken the time to translate the above query into MySQL compatible formatting.

   MySQL

   SELECT
       concat(p.pkey, '-', i.issuenum) AS "KEY",
       i.summary,
       i.reporter,
       i.assignee,
       t.pname                              AS issuetype,
       date_format(i.created,'%d/%m/%y %T') AS created,
       date_format(i.updated,'%d/%m/%y %T') AS modified,
       i.description,
       date_format(i.duedate,'%d/%m/%y %T') AS duedate,
       i.environment,
       s.pname AS priority,
       i.resolution,
       i.resolutiondate,
       w.status,
       i.timeestimate AS originalestimate,
       i.timeestimate AS remainingestimate,
       i.timespent
   FROM
       jiraissue i
   JOIN
       project p
   ON
       i.project = p.id
   JOIN
       issuetype t
   ON
       i.issuetype = t.id
   JOIN
       os_currentstep w
   ON
       i.workflow_id = w.entry_id
   JOIN
       priority s
   ON
       i.priority = s.id
   WHERE
       p.pkey = '<Enter Project Key Here>'
   INTO
       OUTFILE '/tmp/issues.csv'FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

   
   

   
    This only applies to Jira version 6.x and above, for Jira version 5.x and below, you will need to tweak the key column by selecting only pkey from jiraissue table.

2. You can actually import the CSV file generated first to your instance to check the result, but recommendation is to create a test project to test the CSV import, rather than doing it on your actual project. The mapping that I have used is as follow:

   {
     "field.environment" : "environment",
     "date.import.format" : 
     "field.modified" : "updated",
     "user.email.suffix" : "@",
     "field.resolutiondate" : "resolutiondate",
     "field.originalestimate" : "timeoriginalestimate",
     "mapfromcsv" : "false",
     "project.name" : "<Enter Project Key Here>",
     "field.summary" : "summary",
     "value.status.Open" : "1",
     "project.lead" : "<Enter username>",
     "field.timespent" : "timespent",
     "field.assignee" : "assignee",
     "date.fields" : "created",
     "field.reporter" : "reporter",
     "field.resolution" : "resolution",
     "project.key" : "<Enter Project Key Here>",
     "field.duedate" : "duedate",
     "field.remainingestimate" : "timeestimate",
     "field.status" : "status",
     "field.priority" : "priority",
     "field.key" : "issuekey",
     "field.created" : "created",
     "field.description" : "description",
     "field.issuetype" : "issuetype"
   }

    Replace all information in the '< >' quote to the actual value

Step 2 : Exporting Comments

1. Execute the below query to export issues, replacing <Enter Project Key Here> with the project you wanted to export. EG: JSP, JRA, TEST and etc. Additionally, replace <Enter Location to Save File> to the location desired for storing the CSV file generated. (Note: The user running Postgres must have permission to create file on the folder specified).

    copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, (to_char(c.created,'dd/MM/yy HH24:MI') || ';' || c.author || ';' || c.actionbody) as comment
   from jiraissue i, project p, jiraaction c
   where i.project = p.id 
   and c.actiontype = 'comment'
   and c.issueid = i.id
   and p.pkey = '<Enter Project Key Here>')
   to '<Enter Location to Save File>' with CSV HEADER FORCE QUOTE comment;

   Edit: For those of you who are trying to execute this in a MySQL database, we've taken the time to translate the above query into MySQL compatible formatting.

   MySQL

   SELECT
       concat(p.pkey, '-', i.issuenum) AS "KEY",
       i.summary,
       concat(date_format(c.created,'%d/%m/%y %T'), ';', c.author, ';', c.actionbody) AS COMMENT
   FROM
       jiraissue i
   JOIN
       project p
   ON
       i.project = p.id
   JOIN
       jiraaction c
   ON
       c.issueid = i.id
   AND c.actiontype = 'comment'
   WHERE
       p.pkey = '<Enter Project Key Here>'
   INTO
       OUTFILE '/tmp/comments.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

   
   
    This only applies to Jira version 6.x and above, for Jira version 5.x and below, you will need to tweak the key column by selecting only pkey from jiraissue table.

2. The below mapping is then used:

   {
     "project.key" : "<Enter Project Key Here>",
     "field.key" : "issuekey",
     "date.import.format" : "dd/MM/YY HH:mm",
     "field.comment" : "comment",
     "user.email.suffix" : "@",
     "mapfromcsv" : "false",
     "project.name" : "<Enter Project Key Here>",
     "project.lead" : "<Enter username>",
     "field.summary" : "summary"
   }

    Replace all information in the '< >' quote to the actual value

Step 3 : Exporting Attachments

1. Execute the below query to export issues, replacing <Enter Project Key Here> with the project you wanted to export. EG: JSP, JRA, TEST and etc. Additionally, replace <Enter Location to Save File> to the location desired for storing the CSV file generated. (Note: The user running Postgres must have permission to create file on the folder specified).

   copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, (to_char(a.created,'dd/MM/yy HH24:MI') || ';' || a.author || ';' || a.filename || ';' || 'file://' || p.pkey || '/' || p.pkey || '-' || i.issuenum || '/' || a.id ) as attachment
   from jiraissue i, project p, fileattachment a
   where i.project = p.id 
   and a.issueid = i.id
   and p.pkey = '<Enter Project Key Here>')
   to '<Enter Location to Save File>' with CSV HEADER FORCE QUOTE attachment;

    This will only works if you intend to import to a Jira Server instance, no longer works for Jira Cloud CSV import

   Edit: For those of you who are trying to execute this in a MySQL database, we've taken the time to translate the above query into MySQL compatible formatting.

   MySQL

   SELECT
       concat(p.pkey, '-', i.issuenum) AS "KEY",
       i.summary,
       concat(date_format(a.created,'%d/%m/%y %T') , ';' , a.author , ';' , a.filename , ';' ,
       'file://' , p.pkey , '/' , p.pkey , '-' , i.issuenum , '/' , a.id ) AS attachment
   FROM
       jiraissue i
   JOIN
       project p
   ON
       i.project = p.id
   JOIN
       fileattachment a
   ON
       a.issueid = i.id
   WHERE
       p.pkey = '<Enter Project Key Here>'
   INTO
       OUTFILE '/tmp/attachments.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

   
   

    This only applies to Jira version 6.x and above, for Jira version 5.x and below, you will need to tweak the key column by selecting only pkey from jiraissue table.

Reference

The additional documentation may provide further insights into how to import. As this is strictly unsupported, we would encourage you to utilise Atlassian Answers for further assistance as someone from the developer community may be able to offer assistance. Also check out the related link below if you need more information:

• Importing data from CSV
• Commonly asked CSV questions and known issues