How to prepare a database export for a CSV Import from a Jira Server application to a Jira Cloud application
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.
Note
This KB article is obsolete. It was created several years ago, at a time before other tools existed for Jira to export that data.
- For users looking to migrate from Server to Cloud, we recommend using the Jira Cloud Migration Assistant now. It allows you to migrate specific projects into Jira Cloud without destroying existing data on the Cloud site.
- For exporting data from Jira Server into CSV, Jira 7.2 and higher have a native CSV export option now within the issue navigator see - JRASERVER-8580Getting issue details... STATUS
- Both options here are able to export far more issue data than just Issue, comment, attachments that these SQL queries are restricted to.
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.
CSV Import does not cover issue history by default, the following items are covered in this documentation:
- Issue
- Comment
- Attachments*
What is not covered in this documentation:
- component
- label
- affected version
- fixed version
- watchers
- votes
- 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
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;
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 onlypkey
fromjiraissue
table.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
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;
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 onlypkey
fromjiraissue
table.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
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
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
fromjiraissue
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: