Fetch data from JIRA to excel

Hi. I am trying to count number of bugs which are reported every week in specific projects with JQL (I am not sure how to get count only). and then I want to get this number in excel, so that I can see how many bugs have been reported, in comparison to last week, last month, last year. And I am drawing excels graphs using that data. Excel is storing my data and drawing graphs for me but I need to have data from JIRA automatically based on filters, I apply.

So I need counts of bugs in projects (not bugs), historic data and how to fetch them directly from excel so that I don't need to go to JIRA. I trying to automate my reports. We have JIRA version 6.4.9

I hope I am able to describe my question and now waiting for response.

 

Thank you.

6 answers

You can easily and automatically import Jira Cloud data directly into Excel by following these steps:

 

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path.  Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog.  Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet.

 

From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

This is exactly what I needed. I had been downloading the CSV, putting it into a folder and connecting to it that way. It never occurred to me that I could directly link Power Query to JIRA through a Web data connection. Brilliant! This saves me a huge amount of time.

If you enter you credentials wrong or need to change them, it's not easy to find as you're not prompted again after entering them. Here's how to fix it. I'm using Excel 2016 (Office 365):

  1. Right click your Power Query entry in "Queries & Connections" select Edit
  2. Click "Data source settings". A list of data sources is displayed.
  3. Find the data source you want to modify and select "Edit Permissions"
  4. Under "Credentials" you can change your login type, from using Windows Credentials, to Basic (to enter your own username/password) etc.
  5. For Privacy Level, I used "Private" in my setting.

Hope that helps and thanks for the info!

The path does not seem to work with JIRA v6.4.13.  My filter is in the format: https://mycompany.atlassian.net/jira/issues/?filter=39787

would someone know the corrsponding CSV path?

I tried various choices like https://mycompany.atlassian.net/jira/sr/jira.issueviews:searchrequest-csv-current-fields/39787/SearchRequest-39787.html  but no luck.  

searchrequest-printable works but it creates 2 rows if I include the labels column so wanted to try CSV.

The URL's have not changed for me.  Not sure why your path is different. The way I determined the CSV path was by looking at the network traffic in Fiddler when I clicked the link to download a CSV of the report.  You can use the same technique.

Thanks I don't see an option to download csv files in the jira instance we have.  Perhaps it has been disabled. 

Peter,

Your URL needs to have .CSV at the end of it instead of .HTML . 

From reading other people's posts, the export to .csv option is no longer enabled by default, your company's JIRA admin would need to enable the option.

I've also tried to fetch data from the export xls. option, and it does not work.

Nope, wrong.  Export to CSV is fully enabled in all all Server versions of Jira, and still available on the last Cloud system I looked at a couple of minutes ago.

What are you looking at?

Hi Guys, Me too trying the same. But i am getting error as "The command you selected is not available from this shortcut menu." while clicking import option. But using the URL i am able to download the csv successfully.

Steps followed:

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path.  Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog.  Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet.

Please help me in this 

That's not a native Jira message, which suggests an add-on is in use.  I'd look at the docs for that (If you don't have access to something in Jira, it hides it, not blocks it after selection)

Did you find the solution to this problem? I am also running into the same error. 

I am having an issue with this as well, but I am not getting an error. My power query in excel is setup correctly and I can successfully pull the same CSV  from other URLs, but when I try to connect to Jira the data does not populate; the preview always shows up empty.

Above it was mentioned that I should not be blocked by any permissions, but I believe that there is some authentication issue at hand and I can't figure it out.

 

Below are the steps I used in detail:

 

  1. Login to jira.mycompany.com
  2. Saved a filtered list of issues within the my project (https://jira.mycompany.com/issues/?filter=12345)
  3. Selected Export dropdown menu
  4. Right-clicked on the CSV (Current fields) option and select Inspect
  5. Copied the CSV URL (https://jira.mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/12345/SearchRequest-12345.csv)
  6. Open Excel Workbook
  7. Select the Data tab
  8. Select Get Data -> From Web
  9. Pasted the CSV URL in the Web Address box (Basic selected), clicked OK

 

From here, the preview window was showing up empty so I made sure that I had the correct permissions set by doing the following:

  1. Select Edit on the preview window
  2. Select Data source settings
  3. Select Edit Permissions
  4. Select Edit (Permissions were already set to Basic)
  5. Select Basic from the side panel on the left
  6. Entered jira.mycompany.com credentials
  7. Click Save and closed the windows, returning to the Power Query Editor
  8. Click Refresh Preview

 

Any insight on how to resolve this problem?

i'm following the same steps, but in the preview is showing HTML code instead of the CSV information, if i go to the link https://mycompany/jira/sr/jira.issueviews:searchrequest-csv-current-fields/51155/SearchRequest-51155.csv the CSV is downloaded correctly and without any issues, but when i copy that link to the power query option in excel it returns HTML code, does anyone knows how can i solve this? 

 

thank you.

Try adding the "?" to the end of your URL

@Ken Parry  i'm having the same issue doing this: 

"https://mycompany/jira/sr/jira.issueviews:searchrequest-csv-current-fields/51155/SearchRequest-51155.csv?"

is there an specific position i need to add the "?" ?

The ? is at the end as you show.. 

make sure you are using:

Select: New Query->From Other Sources-> From Web

 

Then I have :

File Origin: 65001: unicide (UTF-8)

Delimiter: Comma

Date Type Detection: Based on first 200 rows

 

Hope this helps

I was having problems even getting it to download from the URL.  I went back to the app and did a right click on the download as CSV link and found that there was a limit added to the end of the URL in Jira.  I guess you can't download more than 1000 rows at a time (certainly when I tried 2000 it didn't work).

?tempMax=1000 was appended to the URL:

https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11866/SearchRequest-11866.csv?tempMax=1000

Answer from Dave Ahlers above worked for me after I used Fiddler to get the exact query "https://MyCompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-all-fields/12345/SearchRequest-12345.csv?"  _ I was then able to use the query editor to remove the columns that I did not want... Thanks so much!

I am a novice on using JIRA and have only basic understanding of Fiddler. Could you help me with a couple of hints on how you used fiddler to capture that URL, please?

Appreciate the help

Sujit

You could just enable develop console and view the console to see what the sent code is.. for instance in Chrome it's Ctrl+Shift+I to enable  - look for the Console Window

0 vote
Timothy Chin Community Champion Sep 02, 2015

You'll probably be able to find some reporting add ons in the marketplace:

For example:

 

 

getIssueList from JIRA Command Line Interface (CLI) produces a CSV file that can be imported into Excel.

SQL for JIRA is a standard SQL API for reporting on JIRA with support for SQL aggregation functions like COUNT, SUM, etc. It also supports JIRA JQL, therefore you can execute:

SELECT COUNT(*) FROM ISSUES WHERE JQL = 'project=MYPROJECTID and type = Bug' 

The database SQL query above will execute the JIRA JQL query and count the number of issues returned.

You could write a small Java program to save the results in a file with CVS format, for instance.

SQL for JIRA is really powerful add-on and it would be able to provide all the data sequence at once (all the bugs for a project grouped weekly for a period of time).

SQL for JIRA  can be integrated seamlessly with Birt Reports for JIRA if you want to create a nice chart graph and display it on a JIRA Dashboard.

In this way, you would be able to see the graph up to date on JIRA itself at any time.

Use the Better Excel Plugin that makes creating this report easy:

  1. Write all issues to include in the report to a worksheet (make sure you export the project and creation date for each issue)
  2. Calculate the creation year, month, week from the creation date using Excel functions
  3. Create a pivot chart from this worksheet, using the project field on the horizontal axis, and the creation year, month, week on the other
  4. Draw a pivot chart from the pivot table

If you have the working template, you can refresh this any time using the latest issue data.

 

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

94 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you