Jira issues export in Excel - Formatting

[Giulio Pengo]

Hello there,

I'm trying to export a list of Jira issues in an Excel file.

However, I cannot find the proper method to make them look properly formatted.

I'll try to recap my steps as well as detail my needs, so that it might be easier to find a proper solution.

1. enter Jira software development project
2. enter issues subsection
3. select 2+ issues (same type)
4. click on "export"
5. select any of those results (.csv, excel csv etc) that is compatible with Excel

The "my default CSV" export for example is:

• good because it keeps fields following up one another just like they were following columns...but
• bad because they don't. They're all "pasted" inside the first column.

The "all fields CSV" export does not meet my needs.

I need that every issue field is represented in a column.

However it might seem that either one of these would apply:

1. this is not supported;
2. I'm not skilled enough to operate the excel file properly (perfectly legit);
3. I'm doing something wrong;
4. there's some other hidden problem.

Thank you in advance for your help!

[Philipp Sendek · Accepted solution]

Hi @Giulio Pengo ,

as @Dan Breyen stated, it's downloaded as CSV file. Sometimes Excel is smart enough to format it into columns itself, but sometimes not.

When the same thing happens to me, I'm gladly taking a shortcut that Excel provides for such situations. Open the file and if it looks like in your screenshot (everything in the first column) and proceed like this:

• Click on the header of the first column (The A) so that the full column is selected
• Click on the "Data" tab in Excel
• Click on the "Text to Columns" button:
  
  
• This now opens the "Convert Text to Columns Wizard"
  • Select "Delimited" and click Next
  • Select the correct Delimiter (usually Comma or Semicolon). You know you've selected the right one when the preview at the bottom shows your values properly listed in Columns.
  • (Optional) Click Next if you want to defined Formats for some Columns
  • Click Finish

You should now have your values properly formatted into Columns.

Greetings
Philipp

[Giulio Pengo]

It worked great, thanks Philipp!

[Andreas Kemper]

This works as long as you do not use a comma in any text field.
If you do did, Excel cannot separate the columns correctly.

[Aron Gombas _Midori_]

@Giulio Pengo CSV, being a plain text format, can fail like this. Encoding of special characters can be problematic, too. Type of the values can be also a problem, in CSV everything is just text.

All these can be fixed with manual work, but if you are open to trying an app, our Better Excel Exporter app exports real XLSX files, files in the native spreadsheet format. The aforementioned problems simply cannot occur with this approach.

(Discl. it is a paid and supported app developed by our team. Free for 10 users!)

[Giulio Pengo]

Good to know, we'll check it out and consider this as an addition for the team, thanks!

[Dan Breyen]

Hi @Giulio Pengo from your exported data, it looks like the data is properly formatted as a CSV file with the data separated with a comma.  If you load excel, and open your CSV (outside of Jira) do you get a Text Import Wizard? If so, and you walk through the wizard, it should let you select 'comma' as a delimiter, and when you finish, it should open the file in Excel with the proper columns.

I can't read your first screenshot, as the words are too small. I'm gathering that's your data displayed, with the fields separated by a comma.

When I double click on a CSV file, it's automatically sorting the columns for me, but I would guess if you can get to that Text Import Wizard, you'll be good to go.

Hope that helps.

[Giulio Pengo]

Hi Dan, thank you for your help!

It appears that the problem is on the Excel side - which I never used - not on the Jira side. Therefore it was probably a mistake to open this ticket.

Your suggestion didn't work unfortunately, but it helped me think of a workaround. Here's what I did next:

1. uploaded the file on google drive
2. opened it with Google Sheets
   1. formatting was correct already
3. downloaded it in .xls.
4. opened it in Excel
   1. formatting was correct

This worked, while simply saving the file in .xlsx format from inside Excel did not help in fixing the problem.

Guess this can be closed.

Thank you for your help @Dan Breyen , really appreciated!

[Philipp Sendek]

Hi @Giulio Pengo

perfect, glad it worked!

There's actually a second - even more hidden  - easier way to get a real Excel file from your results:

• Select Export -> Export HTML report (either of the two available options)
• Change the file extension of this resulting *.html file to *.xls (caution: Not xlsx)

When you open that file in Excel now, it is a beautiful table including Header graphic, name of the filter and your results neatly in columns.

Greetings
Philipp

[Giulio Pengo]

Very nice, this works fine as well! :D

[Philipp Sendek]

Deleted because of a double post as the first answer didn't show up after posting. :-)