How to export issues as a csv file?

[Yusuf Ertekin]

I want to export all issues as a csv to sync jira issues with our agile tool. Can you show the way to export?

[Renjith Pillai · Accepted solution]

The file exported by JIRA is actualy html renamed as xls. So like Harry says, you need to open it in Excel and then save as CSV and import it in the Agile tool. If you are looking at automated ways of doing it go for REST API of JIRA.

[Yusuf Ertekin]

Thanks Renjith for your help. I tried to produce csv file by using excel but as Nic said, excel doesn't produce a real csv file that we can use. Our agile tool does not have entegration with REST API of JIRA. If it does, that would be a perfect solution. Thanks again.

[Renjith Pillai]

I am not sure what error you are having in the CSV. I just saved from JIRA. Opened in xls, saved it as xlsx first, then saved the xlsx as CSV, and it is all perfect.

[Yusuf Ertekin]

Yes most probably the problem is not caused by CSV. Our agile tool is not understand csv as they promise. I already sent email them to solve it thanks a lot.

[Mukesh Kumar]

If I understand correctly, when you save the file to CSV all character encoding will be lost. How do you handle this scneario? I'm surprise, JIRA doesn't have a basic CSV export or flat file export utility. I might not have Excel installed on my machine.

[Nic Brough -Adaptavist-]

Renjith's answer isn't *quite* accurate. The export is not html, it's xml which screams "I am a spreadsheet". Although it's labelled with excel and is given an xls extension, it's not excel. It's xml which *any* spreadsheet program can import (ok, there may be some that won't do it, but if they're that old and primitive, I'd question why you're using it). You can even open it in a text editor. The main problem you have here is that the XML is accurate and contains perfectly good encoded data. It's whatever you're using to downgrade it to CSV that is losing the encoding.

[Deleted user]

I'd be interested in knowing how the JIRA REST API automated task would work. It was mentioned in this Comment, but then the conversation digressed to how faulty the "Export to Excel" function works in a JIRA filter. Is there a REST_to_CSV example somewhere?

Thanks!

[Zaldy Parian]

I'm interested to know the exact steps on how to export Issues to CSV.

Can someone please provide the details?

[Jason Wong]

Here's my workaround if you don't have Excel and use a Mac:

1. Export to Excel
2. Rename the file to html. 
3. Load it in safari. 
4. Highlight the entire table.
5. Copy it (cmd + c). 
6. Open Numbers and a new spreadsheet.
7. Paste!

[Deleted user]

Thanks Jason! That worked for me! :)

[Diego Machado]

O my gosh!!! thanks for the tip Jason.

[IT nKey]

Very useful, thanks!

[Scott Bullard]

+1 for this workaround hack. Looking at the roadmap it appears that Atlassian has no plans to provide a simple CSV export.

[Jeremy Robin]

Yes!

[Tanya Partem]

How the HECK do I execute step #1 on this workflow? There's no Export button or anything similar... 

[Jose Fernandes]

I'm using a Mac and Pages and hating Jira every second along the way. How in the world can't you export a table to CSV, by far the most supported format?

[carlgieringer]

Well, that would make it easy for customers to export their data and leave the JIRA platform...so no one told me this, but I'm guessing vendor lock-in is one explanation for the feature not being high on their list.

[Nic Brough -Adaptavist-]

No, it's not that, there's just no reason to do it when you can export to xml or spreadsheet-readable xml.  You simply don't need CSV - xml is better, and a doddle to convert to CSV if you really must.

Anyone moving off JIRA wouldn't touch CSV anyway, the data is far too rich and there are much better ways of moving data to other systems.

[Bob Swift]

JIRA Command Line Interface produces a csv file with the getIssueList action.

[Jan Nielsen]

Take Bob's advice.

So, like Yusef Ertekin, I'm trying to export issues from my JIRA 6 instance to CSV for reporting purposes. After setting the appropriate export limits and bouncing JIRA [1], my 10k issues turned into a 50MB "Microsoft Excel" file.

My workstations are Linux as are my servers - I don't own a Microsoft Windows or Microsoft Excel. However, after an hour or so on my capable Linux development machine, LibreOffice 4 converted this 50MB file into a 12MB CSV file. Sadly, about a third way through the CSV file, the conversion failed resulting in jumbled CSV records - so the file is junk. From viewing the data in LibreOffice, it appears the JIRA export is corrupt. Unsurprisingly, the CLI invocation of LibreOffice results in the same mess.

The xsl2csv utility fails after a few seconds, complaining of an array problem, so that's a dead end. [2] Gnumeric's ssconvert also fails to parse this "Microsoft Excel" file [3], including the HTML and XML modes. It sure smells like the "Excel (All Fields)" output might not be what is purports to be... [4]

An RFC-4180 compliant CSV export [5] would be much more valuable to me and, I suspect, to most users. Non-Windows platforms have parsers, incluing CLI-based, that can handle pretty darn anything close to RFC-4180 and Microsoft Excel handles RFC-4180 just fine. I'm no spreadsheet guru, but I don't see any gain from having "Excel" instead of "CSV" - just the opposite, actually.

I tried Bob's JIRA Command Line Interface. [6] It took 5 minutes to install and it worked on the initial try; a few minutes later I got my 10k issues delivered in a CSV file with the getIssueList 'action' that I can manipulate in AWK.[7] Thanks, Bob!

Cheers,

Jan

[1] https://confluence.atlassian.com/display/JIRAKB/Export+More+Than+1000+Results+to+Excel

[2] "Can't use an undefined value as an ARRAY reference at /urs/bin/xls2csv line 139."

[3] "htmlParseEntityRef: expecting ';'"

[4] As a long-time corporate licensee (as well as user of multiple open-source projects leveraging JIRA and Confluence), I am disappointed in Atlassian's choice of "Excel" over "CSV". If you have technical grounds for this mis-step you should clearly publish why you would choose this over CSV.

[5] http://tools.ietf.org/html/rfc4180

[6] https://bobswift.atlassian.net/wiki/display/ACLI/3.5.0+-+Help+text

[7] I'm embarassed I spent an 90 minutes on the Excel work-arounds...

[Jan Nielsen]

It also appears that submission from my Linux machines drops all formatting...so, this last one came from a Windows machine I scrounged up. Is that really how you want this to work, Atlassian?

[Nic Brough -Adaptavist-]

Their choice was probably determined by the fact that XML supports more clever functions (including the ability to say "Hey, I'm a proper spreadsheet" to any program trying to open it) than CSV. It is quite important to understand that the file you get is NOT an Excel file if you're going to be using exports a lot.

That, coupled with the sad fact that most desktop users are lumbered with Windows and have Excel as their spreadsheet makes it by far the most used choice. Not the best choice, but as most exports are going to end up in Excel, then it makes sense.

It's not hard to create other "views" though. I did a CSV view for a client ages ago. No idea if I was properly compliant with the CSV standard, but it worked well for them. You could easily whack the export through an xml transform too, that can do very powerful different things.

[Mark Biro]

Hi there,

With add-on JExcel,  you can import and export your issues (even by copy and pasting the selected cells) with 1 click! And its free.

Feel free to give it a try:
https://marketplace.atlassian.com/plugins/com.moresimp.jexcel/server/overview

Hope it will help you, too.

Best Regards, Mark 

[Midori]

Better Excel Plugin produces real Excel files (in XLSX format), unlike the built in Excel export (which, in fact, produces HTML files).

The exports are generated from smart Excel template files, so if you have a large number of issues, but don't need all properties, you can easily trim it only to those columns that you need. It has real data types, it supports Open Office (Linux, Mac), and it has many advantages over the built-in export (which works fine in simpler situations, we must admit).

Try it free with your own data

jira-excel-export.png

[Jessica Palmer]

I too am a Mac user. Is Jira planning on making any accommodations for non-windows users?

[Nic Brough -Adaptavist-]

There's no need, as already stated above. The "excel" output is xml that announces "I'm a spreadsheet" and can be read by any spreadsheet programs that have xml parsing capabilities (all the ones I've used on a Mac do)

Try the export. You migth need to assiciate the downloaded file type with your chosen spreadsheet, but it'll work fine.

[Jan Nielsen]

@Nic Brough:

To be clear, in my use-case (described above), the process is fully scripted and my spread-sheet application (LibreOffice 4.0) did not correctly convert the JIRA's "Excel" format to CSV. The tools from Bob Swift, however, did correctly convert to CSV.

The point of my comment is: CSV is correctly handled by all spreadsheets whereas JIRA's "Excel" format is not, as my use-case clearly demonstrates.

[Nic Brough -Adaptavist-]

That's odd, I've been using LibreOffice on a Mac for ages, and the "excel" downloads have worked fine for me. Ive not had to do anything odd (that I know of) to configure it, just opened the downloded file.

(I'd always say "if in doubt, use Bob's code" though - it's never let me down, and I've never run into it breaking for anyone else either)

[Harry Chan]

Hi, go to the issue navigator, do a search for the issues you need - you can search for all issues this way too.

Near the top right is a dropdown to export to excel. You can then save this as a csv.

[Yusuf Ertekin]

Thanks for your help. I already tried this solution but our agile tool did not recognize it and I doubted about the correctness of the solution. As a result, I think problem is caused by our agile tool thanks again.

[Nic Brough -Adaptavist-]

Depending on the spreadsheet program you've used to open the file from Jira, you may not actually have a csv file as output. When you use the "save" function in there and get the csv file, it's worth checking it by opening it with a plain text editor and checking that it really is csv. If it's not, that would explain why your tool can't read it, and you may want to retry the save-as function (or possibly "export as csv")

[Yusuf Ertekin]

Nic thanks for your help. I checked the csv file that was produced by excel as you said, it is not a real csv. I tried to save as again but nothing changed. I could not find a selection to export as csv neither in excel nor in Jira.

[Nic Brough -Adaptavist-]

I don't know what version of Excel you're using (and I don't have a copy to hand either, never use it any more). You need to do one of two things:

1. Use the "save as" option but make sure you change the file format to csv.
2. Use the "export" option to export the file (I may be thinking of non-excel spreadsheets here, so look for "save as" first)

Just changing the file name is NOT enough, you have to specify the style of output.

[Yusuf Ertekin]

I tried save-as function with changing both file name and type. I tried 3 types of CSV to save-as file as a csv which are .csv(Macintosh), .csv(Comma delimited), and .csv(MS-DOS). Neither of them was accepted by agile tool and they also does not seem like real csv.

[Nic Brough -Adaptavist-]

.csv (comma delimited) is the one I'd go for, although I suspect all three are perfectly valid comma separated variable files (again, you can check in a plain text editor - you should see one very long row per record, with commas between fields).

It's starting to sound like your agile tool doesn't understand CSV...

[Yusuf Ertekin]

Yes both of them are exactly like you said. :) I start to feel same way, I think that I should mail them to learn about what kind of csv file that they claim to be understood by tool or does it really understand csv. Thanks a lot. Have a nice day.

[Marcin Zukowski]

Wow, tried getting a large number of issues in XLS. The document was so large/complex my Excel died on it.

Atlassian, really, no CSV or TSV export? That's plain silly - comparing to the complexity of RTF/XLS export, this is trivial to implement, and for most users exactly what they need. 

[Nic Brough -Adaptavist-]

Excel isn't actually excel, it's xml with tags to tell the importing program that it is a spreadsheet. You can open it in anything, although it makes most sense to do it in a spreadsheet. Then click "save as csv".

[Marcin Zukowski]

Like I wrote - Excel on my Mac doesn't even open it.

[Hunter Blanks]

I'm seeing the same problem in my account right now, with the current version of Numbers. An XLS file exported from one of my saved filters (10300, with "Current Fields") gives me an HTML file instead of an XLS file.

Amusedly, this HTML file is actually a version of the "Printable" HTML page, apparently formatted for Microsoft Office. Here's the header:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<title>Finished deployments (shortened) (JIRA)</title>
<style type="text/css">
table {
    mso-displayed-decimal-separator:"\.";
    mso-displayed-thousand-separator:"\,";
}
body
{
    margin: 0px;
    font-size: 12px;
    font-family: Arial, sans-serif;
    color:black;
}

Can you all please fix this? I don't think it's a lot to ask that we should be able to download a CSV file of an issues list, or at least an XLS file that works in non-Excel spreadsheets.

[Nic Brough -Adaptavist-]

We're a community of end-users. We can talk about Atlassian problems, and suggest workarounds, and even suggest code to Atlassian. But we're not Atlassian, and we can't fix anything in their code, we're dependent on them doing it.

There are plenty of workarounds mentioned above. Not ideal, as you suggest it should be in the core product, but as "Excel format" works for the overwhelming majority of users, and there are simple workarounds, I suspect it's going to remain in the "unloved" pile.

[Nic Brough -Adaptavist-]

There are higher priorities.

If you want an example of a really old issue that eventually got fixed, look at JRA-9 ;-) And that only made it into the fix list because there were no workarounds at all. Things with workarounds are going to be even less loved!

[Hunter Blanks]

Nic,

Thanks for the quick reply. Indeed, this corresponds to an issue that's been active with Atlassian since 2006:

https://jira.atlassian.com/browse/JRA-8580

Seven years in the unloved pile sure is a long time, though.

[Jose Fernandes]

I'm using Numbers from iWork 09 on a Mac and I'm pretty sure that your "Excel" format (no problem if it was actual xls data) doesn't work. It really makes you guys look bad, I mean, I'm a developer, it's CSV were talking about. You know how handy it is, and this is usually a dozen lines of code plus setting the links in the pages, so why all the fuss getting this done? Really, dumping a 2-dimensional array with commas and new lines in the middle used to take less than posting a reply in this thread.

[Jan Nielsen]

Take Bob's advice. Like Yusef Ertekin, I'm trying to export issues from my JIRA 6 instance to CSV for reporting purposes. After setting the appropriate export limits and bouncing JIRA, my 10k issues turned into a 50MB "Microsoft Excel" [1] file. My workstations are Linux as are my servers - I don't own a Microsoft Windows or Microsoft Excel. However, after an hour or so on my capable Linux development machine, LibreOffice 4 converted this 50MB file into a 12MB CSV file. Sadly, about a third way through the CSV file, the conversion failed resulting in jumbled CSV records - so the file is junk. Unsurprisingly, the CLI invocation of LibreOffice results in the same mess. The xsl2csv utility fails after a few seconds, complaining of an array problem, so that's a dead end. [2] Gnumeric's ssconvert also fails to parse this "Microsoft Excel" file [3], including the HTML and XML modes. It sure smells like the "Excel (All Fields)" output might not be what is purports to be... [4] An RFC-4180 compliant CSV export [5] would be much more valuable to me and, I suspect, to most users. Non-Windows platforms have parsers, incluing CLI-based, that can handle pretty darn anything close to RFC-4180 and Microsoft Excel handles RFC-4180 just fine. I'm no spreadsheet guru, but I don't see any gain from having "Excel" instead of "CSV" - just the opposite, actually. I tried Bob's JIRA Command Line Interface. [6] It took 5 minutes to install and it worked on the initial try; a few minutes later I got my 10k issues delivered in a CSV file that I can manipulate in AWK with the getIssueList 'action'.[7] Thanks, Bob! Cheers, Jan [1] https://confluence.atlassian.com/display/JIRAKB/Export+More+Than+1000+Results+to+Excel [2] "Can't use an undefined value as an ARRAY reference at /urs/bin/xls2csv line 139." [3] "htmlParseEntityRef: expecting ';'" [4] As a long-time corporate licensee (as well as user of multiple open-source projects leveraging JIRA and Confluence), I am disappointed in Atlassian's choice of "Excel" over "CSV". If you have technical grounds for this mis-step you should clearly publish why you would choose this very poor choice over CSV. [5] http://tools.ietf.org/html/rfc4180 [6] https://bobswift.atlassian.net/wiki/display/ACLI/3.5.0+-+Help+text [7] I'm embarrassed I spent an 90 minutes the Excel work-arounds...

[Jan Nielsen]

Take Bob's advice. Like Yusef Ertekin, I'm trying to export issues from my JIRA 6 instance to CSV for reporting purposes. After setting the appropriate export limits [1] and bouncing JIRA, my 10k issues turned into a 50MB "Microsoft Excel" file. My workstations are Linux as are my servers - I don't own a Microsoft Windows or Microsoft Excel. However, after an hour or so on my capable Linux development machine, LibreOffice 4 converted this 50MB file into a 12MB CSV file. Sadly, about a third way through the CSV file, the conversion failed resulting in jumbled CSV records - so the file is junk. Unsurprisingly, the CLI invocation of LibreOffice results in the same mess. The xsl2csv utility fails after a few seconds, complaining of an array problem, so that's a dead end. [2] Gnumeric's ssconvert also fails to parse this "Microsoft Excel" file [3], including the HTML and XML modes. It sure smells like the "Excel (All Fields)" output might not be what is purports to be... [4] An RFC-4180 compliant CSV export [5] would be much more valuable to me and, I suspect, to most users. Non-Windows platforms have parsers, incluing CLI-based, that can handle pretty darn anything close to RFC-4180 and Microsoft Excel handles RFC-4180 just fine. I'm no spreadsheet guru, but I don't see any gain from having "Excel" instead of "CSV" - just the opposite, actually. I tried Bob's JIRA Command Line Interface. [6] It took 5 minutes to install and it worked on the initial try; a few minutes later I got my 10k issues delivered in a CSV file that I can manipulate in AWK with the getIssueList 'action'.[7] Thanks, Bob! Cheers, Jan [1] https://confluence.atlassian.com/display/JIRAKB/Export+More+Than+1000+Results+to+Excel [2] "Can't use an undefined value as an ARRAY reference at /urs/bin/xls2csv line 139." [3] "htmlParseEntityRef: expecting ';'" [4] As a long-time corporate licensee (as well as user of multiple open-source projects leveraging JIRA and Confluence), I am disappointed in Atlassian's choice of "Excel" over "CSV". If you have technical grounds for this mis-step you should clearly publish why you would choose this very poor choice over CSV. [5] http://tools.ietf.org/html/rfc4180 [6] https://bobswift.atlassian.net/wiki/display/ACLI/3.5.0+-+Help+text

[Jan Nielsen]

Ugh - apparently formatting is not possible...? Can that really be? Or is it just from non-Windows?

[Jan Nielsen]

Take Bob's advice. Like Yusef Ertekin, I'm trying to export issues from my JIRA 6 instance to CSV for reporting purposes. After setting the appropriate export limits and bouncing JIRA, my 10k issues turned into a 50MB "Microsoft Excel" [1] file. My workstations are Linux as are my servers - I don't own a Microsoft Windows or Microsoft Excel. However, after an hour or so on my capable Linux development machine, LibreOffice 4 converted this 50MB file into a 12MB CSV file. Sadly, about a third way through the CSV file, the conversion failed resulting in jumbled CSV records - so the file is junk. Unsurprisingly, the CLI invocation of LibreOffice results in the same mess. The xsl2csv utility fails after a few seconds, complaining of an array problem, so that's a dead end. [2] Gnumeric's ssconvert also fails to parse this "Microsoft Excel" file [3], including the HTML and XML modes. It sure smells like the "Excel (All Fields)" output might not be what is purports to be... [4] An RFC-4180 compliant CSV export [5] would be much more valuable to me and, I suspect, to most users. Non-Windows platforms have parsers, incluing CLI-based, that can handle pretty darn anything close to RFC-4180 and Microsoft Excel handles RFC-4180 just fine. I'm no spreadsheet guru, but I don't see any gain from having "Excel" instead of "CSV" - just the opposite, actually. I tried Bob's JIRA Command Line Interface. [6] It took 5 minutes to install and it worked on the initial try; a few minutes later I got my 10k issues delivered in a CSV file that I can manipulate in AWK with the getIssueList 'action'.[7] Thanks, Bob! Cheers, Jan [1] https://confluence.atlassian.com/display/JIRAKB/Export+More+Than+1000+Results+to+Excel [2] "Can't use an undefined value as an ARRAY reference at /urs/bin/xls2csv line 139." [3] "htmlParseEntityRef: expecting ';'" [4] As a long-time corporate licensee (as well as user of multiple open-source projects leveraging JIRA and Confluence), I am disappointed in Atlassian's choice of "Excel" over "CSV". If you have technical grounds for this mis-step you should clearly publish why you would choose this very poor choice over CSV. [5] http://tools.ietf.org/html/rfc4180 [6] https://bobswift.atlassian.net/wiki/display/ACLI/3.5.0+-+Help+text [7] I'm embarrassed I spent an 90 minutes the Excel work-arounds...