Exporting Jira's issues using CSV in batches

Still need help?

The Atlassian Community is here for you.

Ask the community

Disclaimer

Atlassian does not support this code below, which is provided "AS IS". The goal of this article is to provide a piece of code that illustrates one way to achieve the desired goal.

Feedback provided at the bottom of the page is appreciated, but won't be handled as support.


Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

    

Summary

When exporting issues using CSV from Jira, it could cause it to run out of memory if we increase the limit as described on this page.

Here we present a simple Python script that iterates over a JQL query, exporting batches of CSV files. This example can be used as is but might be a nice place to start and be built upon.


Even though this script is not officially supported, we'd love to hear feedback, which can be given at the bottom of this page.

Environment

This script requires Python 3 to be installed. The script doesn't have to be run on the server.

How it works

Jira has an endpoint specific for CSV export with all columns:

/sr/jira.issueviews:searchrequest-csv-all-fields/temp/SearchRequest.csv

To this use case, we're mostly interested in some parameters:

  • jqlQuery - a JQL query
  • tempMax - the number of issues to be brought in each batch

  • pager/start - the number of issues to be skipped

With these parameters, we're able to get the issues we want and iterate over the whole query.

(info) Each file is named after the first and last issue key, so we highly recommend to use a JQL query ended in "ORDER BY key"

Expand below to see the script's code, or just download the jql_to_csv.py file.

Code expand...
import requests
import csv
import os
import argparse
import getpass

parser = argparse.ArgumentParser(description='Calls a JQL query and export it to CSV files.\nWe suggest that the JQL queries end with "ORDER BY key"')
parser.add_argument('-j','--jql', nargs='?', default='ORDER BY key', metavar='JQL_query', help='JQL query - default query is "ORDER BY key"')
parser.add_argument('-u', required=True, metavar='username', help='Username')
parser.add_argument('-p', nargs='?', default='', metavar='password', help='Password. If parameter is not passed, the password will be prompted')
parser.add_argument('-n', nargs='?', default=1000, metavar='Number_of_issues', help='Number of issues per CSV batch. Default of 1000 in line with Jiras default. For more details, check https://confluence.atlassian.com/jirakb/filter-export-only-contains-1000-issues-in-jira-server-191500982.html')
parser.add_argument('-U','--url', required=True, metavar='Base_URL', help='Jira''s base URL. For example, https://jira.mycompany.com')

args = parser.parse_args()

jql = args.jql
username = args.u
password = args.p
step = int(args.n)
baseurl = args.url

if password == '':
    password = getpass.getpass()
#print(args)

start=0

url = baseurl+'/sr/jira.issueviews:searchrequest-csv-all-fields/temp/SearchRequest.csv?jqlQuery='+jql

while True:
    print(str(start)+' issues exported')
    theurl = url+'&tempMax='+str(step)+'&pager/start='+str(start)
    resp = requests.get(theurl, auth=(username, password), verify=False)

    f = open('output.csv', 'w')
    f.write(resp.text)
    f.close()

    f = open('output.csv','r',newline='')
    reader = csv.DictReader(f)
    try:
        row = reader.__next__()
    except:
        break

    firstkey = row['Issue key']

    count=1
    for r in reader:
        row = r
        count+=1

    f.close()

    lastkey = row['Issue key']

    os.rename('output.csv',(firstkey+'-'+lastkey+'.csv'))

    if count < step:
        print(str(start+count)+' issues exported')
        break
    
    start+=step

Usage

To check the script usage, just run it with the command:

python3 jql_to_csv.py -h

The result is:

usage: jql_to_csv.py [-h] [-j [JQL_query]] -u username [-p [password]]
                     [-n [Number_of_issues]] -U Base_URL

Calls a JQL query and export it to CSV files. We suggest that the JQL queries
end with "ORDER BY key"

optional arguments:
  -h, --help            show this help message and exit
  -j [JQL_query], --jql [JQL_query]
                        JQL query - default query is "ORDER BY key"
  -u username           Username
  -p [password]         Password. If parameter is not passed, the password
                        will be prompted
  -n [Number_of_issues]
                        Number of issues per CSV batch. Default of 1000 in
                        line with Jiras default. For more details, check
                        https://confluence.atlassian.com/jirakb/filter-export-
                        only-contains-1000-issues-in-jira-
                        server-191500982.html
  -U Base_URL, --url Base_URL
                        Jiras base URL. For example,
                        https://jira.mycompany.com


Examples

Example 1 - export all issues. The password for the user will be prompted, and the issues will be exported in batches of 1000.
The default JQL "ORDER BY key" will be used.

python3 jql_to_csv.py -u myusername --url "https://myjira.mycompany.com"

Example 2 - export issues from the SP project only, passing the password in the command line, in smaller batches this time.

python3 jql_to_csv.py -u myusername -p mypassword -U "https://myjira.mycompany.com" --jql "project = SP ORDER BY key" -n 100



Last modified on Aug 6, 2021

Was this helpful?

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