How to prepare a database export for a CSV Import from a Jira Server application to a Jira Cloud application

Still need help?

The Atlassian Community is here for you.

Ask the community

The content on this page relates to platforms which are not supported. Consequently, Atlassian Support cannot guarantee providing any support for it. Please be aware that this material is provided for your information only and using it is done so at your own risk.

Platform Notice: Cloud Only - This article only applies to Atlassian products on the cloud platform.

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. 

(info) 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.

CSV Import does not cover issue history by default, the following items are covered in this documentation:

  1. Issue
  2. Comment
  3. Attachments*

What is not covered in this documentation:

  1. component
  2. label
  3. affected version
  4. fixed version
  5. watchers
  6. votes
  7. custom fields

for Jira Cloud CSV import attachments, it will only support if the file is accessible from HTTP and HTTPS only, file system method described here will no longer work on Jira Cloud

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'



    (info) 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"
    }

    (info) 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'



    (info) 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"
    }

    (info) 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;

    (info) 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'


    (info) 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:

Last modified on Mar 10, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.