Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-62517

Comma in a text field breaks CSV export

XMLWordPrintable

    • 7
    • 15
    • Severity 2 - Major
    • 377
    • Hide
      Atlassian Update – 28 August 2018

      Hi everyone,

      I am going to resolve the current issue as Not a Bug.
      Our research on the subject of CSV export showed that current problem affects only certain configurations of Microsoft Excel. The investigation demonstrated that other spreadsheet management software is not affected by the reported problem at all.

      Root of the problem

      Depending on the country in which the Microsoft Excel is installed a certain region is assigned to it's configuration.

      The region would determine a decimal point separator - such as used in different currencies. For example you can see a comma and dot as decimal separator for representing different amounts: 123,45 vs 123.45.

      28 August 2018 update

      More delimiters for CSV export were shipped with Jira Software Server 7.9. You can now choose one of the four delimiters when exporting your issues to CSV. These are comma, semicolon, vertical bar, and caret (this one: ^). You can read about other capabilities we added in Jira Software 7.9 in the release notes.

      Impact of CSV export feature on Jira users

      Jira 7.2.0 released featuring new CSV export, which deprecated previous Excel export. This had the following impact:

      • CSV export has different format of the data export which creates frictions in using data just the same as it used to be with Excel export.
      • certain Excel uniqueness with CSV handling delivers sub-optimal user experience for certain regions.

       

      Acknowledgement

      After reading all your feedback around CSV export feature we realised the change introduced unnecessary friction for the simple use cases. On the other hand - supporting a native Jira to Excel would be a significant effort which would limit our ability to deliver on other improvements such as hi-impact bugfixes we deliver now, security bugfix and new features.

      With the feedback provided we could not leave the situation unattended. Which led us to the following solution..

       

      Our solution

      We decided to re-enable the legacy export. This has been done in Jira 7.4.0. https://confluence.atlassian.com/jirasoftware/jira-software-7-4-x-release-notes-902079634.html We have changed the name of the export to 'HTML Export'. That is done as Jira has never supported a native export Excel export: even up until Jira 7.2 data had been always exporting data in the html format but was just changing an export file extension.

      The exported html data can be imported in Excel just as any .xls, .csv, .html file.

      If that creates too much friction - you can still make Jira export an '.xls' file.

      How to make Jira export .xls file instead of .html file

      Please edit file jira-config.properties and make sure the line for jira.export.excel.enabled exists and looks like:

       jira.export.excel.enabled=true

      After file is saved - restart Jira. Export to Excel button should appear in the Issue Navigator along with Export to HTML.

      This workaround works for Jira versions since 7.2.0 upwards (at the moment of writing up until 7.6.0).

       

      Alternative solutions

      Plug-in Better Excel Plugin provides native Excel export via XLSX files.

       

      Feedback

      Please don't hesitate to provide to provide additional feedback on the issues JRASERVER-62517, JRASERVER-63857. Please post your questions, clarifications, etc. Your feedback has helped to change the shape the feature in Jira 7.4.0 and we'll continue to listen to deliver the appropriate solution in the product.

      Thanks for the inputs!

      Cheers,
      Ignat Alexeyenko
      Jira Bugmaster

      Show
      Atlassian Update – 28 August 2018 Hi everyone, I am going to resolve the current issue as Not a Bug. Our research on the subject of CSV export showed that current problem affects only certain configurations of Microsoft Excel. The investigation demonstrated that other spreadsheet management software is not affected by the reported problem at all. Root of the problem Depending on the country in which the Microsoft Excel is installed a certain region is assigned to it's configuration. The region would determine a decimal point separator - such as used in different currencies. For example you can see a comma and dot as decimal separator for representing different amounts: 123,45 vs 123.45. 28 August 2018 update More delimiters for CSV export were shipped with Jira Software Server 7.9. You can now choose one of the four delimiters when exporting your issues to CSV. These are  comma ,  semicolon ,  vertical bar , and  caret  (this one: ^). You can read about other capabilities we added in Jira Software 7.9 in the  release notes . Impact of CSV export feature on Jira users Jira 7.2.0 released featuring new CSV export, which deprecated previous Excel export. This had the following impact: CSV export has different format of the data export which creates frictions in using data just the same as it used to be with Excel export. certain Excel uniqueness with CSV handling delivers sub-optimal user experience for certain regions.   Acknowledgement After reading all your feedback around CSV export feature we realised the change introduced unnecessary friction for the simple use cases. On the other hand - supporting a native Jira to Excel would be a significant effort which would limit our ability to deliver on other improvements such as hi-impact bugfixes we deliver now, security bugfix and new features. With the feedback provided we could not leave the situation unattended. Which led us to the following solution..   Our solution We decided to re-enable the legacy export. This has been done in Jira 7.4.0. https://confluence.atlassian.com/jirasoftware/jira-software-7-4-x-release-notes-902079634.html We have changed the name of the export to 'HTML Export'. That is done as Jira has never supported a native export Excel export: even up until Jira 7.2 data had been always exporting data in the html format but was just changing an export file extension. The exported html data can be imported in Excel just as any .xls, .csv, .html file. If that creates too much friction - you can still make Jira export an '.xls' file. How to make Jira export .xls file instead of .html file Please edit file jira-config.properties and make sure the line for jira.export.excel.enabled exists and looks like: jira.export.excel.enabled= true After file is saved - restart Jira. Export to Excel button should appear in the Issue Navigator along with Export to HTML. This workaround works for Jira versions since 7.2.0 upwards (at the moment of writing up until 7.6.0).   Alternative solutions Plug-in  Better Excel Plugin  provides native Excel export via XLSX files.   Feedback Please don't hesitate to provide to provide additional feedback on the issues JRASERVER-62517 , JRASERVER-63857 . Please post your questions, clarifications, etc. Your feedback has helped to change the shape the feature in Jira 7.4.0 and we'll continue to listen to deliver the appropriate solution in the product. Thanks for the inputs! Cheers, Ignat Alexeyenko Jira Bugmaster

      Summary

      Having a comma in a text field will put values in wrong fields on a CSV exported from JIRA

      Environment

      JIRA server 7.1.x
      JIRA server 7.2.x

      Steps to Reproduce

      1. Create a new issue
      2. Put a comma in any place among the text in the Description field or any other text field
      3. From the issue navigator, export the issue to a CSV file (e.g. Export > CSV (All fields))

      Expected Results

      The CSV file will be created with the correct values for every field

      Actual Results

      Values after the comma will be exported to the wrong field in the CSV file, putting every field values after that in the wrong column in the CSV file

      Workaround

      Certain Microsoft Excel configurations does not correctly handle JIRA CSV export format. This typically depends on the region where product is installed.

      To workaround the problem, one can use the Export to HTML feature that is available in JIRA 7.4.0 and latter.

       

      Step 1. Export the data to HTML from JIRA

       

      Step 2. Import the file into Excel.

      Following options are available:

      • Import Menu available in Microsoft Excel, chose the .html file exported from JIRA.
      • Rename the exported file to .xls extension so it can be opened as ab Excel spreadsheet.

        1. Broken export MS Excel 2016.xlsx
          9 kB
        2. Smart Banka - Backlog (JIRA).csv
          2 kB
        3. workaround-html-export.png
          31 kB

          Form Name

            [JRASERVER-62517] Comma in a text field breaks CSV export

            Ignat (Inactive) added a comment - - edited

            Hi david.stallard1567241156, 

            To our best knowledge, Microsoft is capable of handling xls files which contain HTML content. Apparently I can find this in their update From August 9, 2016:

            Issue recap:

            The Excel team has made a change in the behavior of certain file types to increase security. This change came in the security updates KB3115262, KB3170008, and KB3115322. Previously, when you tried to open an HTML, XLA, or XLAM file with an .XLS file extension from an untrusted location, Excel would warn about the mismatch between the file extension and content, but would still open the workbook without Protected View security. After the security updates Excel no longer will open the workbook because these files are not compatible with Protected View and there is no warning or other indication it was not opened.

            https://social.technet.microsoft.com/Forums/en-US/d2d84793-9920-45e3-baef-5027a4ac1ae0/kb3170008-for-office-2016-breaks-functionality-ms16088

             

            Whereas, mentioned in the article security update kb3115438, contain this piece of information:

            Excel 2016 silently blocks the loading of untrusted HTML files. This issue occurs after you install MS16-088: Description of the security update for Excel 2016: July 12, 2016 (KB3115272). After you install this new update, Excel 2016 will open HTML documents (even if they're renamed as .xls files) in protected view instead of silently failing.

            https://support.microsoft.com/en-us/help/3115438/august-9-2016-update-for-excel-2016-kb3115438

             

            If you would like to find more details on the security - I'd recommend reaching out to Microsoft community and support directly.
            Meanwhile, the Team acknowledges limitations of our solution and we've provided detailed insights and alternatives on how to export Jira issues to excel in the Current Status.

            I'm hoping this information would help you to address exporting issues.

            Cheers,
            Ignat Alexeyenko
            Jira Bugmaster.

            Ignat (Inactive) added a comment - - edited Hi david.stallard1567241156 ,  To our best knowledge, Microsoft is capable of handling xls files which contain HTML content. Apparently I can find this in their update From August 9, 2016: Issue recap: The Excel team has made a change in the behavior of certain file types to increase security. This change came in the security updates KB3115262, KB3170008, and KB3115322. Previously, when you tried to open an HTML, XLA, or XLAM file with an .XLS file extension from an untrusted location, Excel would warn about the mismatch between the file extension and content, but would still open the workbook without Protected View security. After the security updates Excel no longer will open the workbook because these files are not compatible with Protected View and there is no warning or other indication it was not opened. https://social.technet.microsoft.com/Forums/en-US/d2d84793-9920-45e3-baef-5027a4ac1ae0/kb3170008-for-office-2016-breaks-functionality-ms16088   Whereas, mentioned in the article security update kb3115438, contain this piece of information: Excel 2016 silently blocks the loading of untrusted HTML files. This issue occurs after you install  MS16-088: Description of the security update for Excel 2016: July 12, 2016 (KB3115272) . After you install this new update, Excel 2016 will open HTML documents (even if they're renamed as .xls files) in protected view instead of silently failing. https://support.microsoft.com/en-us/help/3115438/august-9-2016-update-for-excel-2016-kb3115438   If you would like to find more details on the security - I'd recommend reaching out to Microsoft community and support directly. Meanwhile, the Team acknowledges limitations of our solution and we've provided detailed insights and alternatives on how to export Jira issues to excel in the Current Status . I'm hoping this information would help you to address exporting issues. Cheers, Ignat Alexeyenko Jira Bugmaster.

            David Stallard added a comment -

            According to the Jira 7.2 release notes, the Excel exporter was removed due to a Microsoft security update which made it so these files could no longer be opened:
            https://confluence.atlassian.com/adminjira/jira-platform-releases/jira-7-2-x-platform-release-notes#JIRA7.2.xplatformreleasenotes-csvCSVexporter(replacestheExcelHTMLexporter)

            Is this no longer a concern, since you've given us a solution to re-enable it?

            David Stallard added a comment - According to the Jira 7.2 release notes, the Excel exporter was removed due to a Microsoft security update which made it so these files could no longer be opened: https://confluence.atlassian.com/adminjira/jira-platform-releases/jira-7-2-x-platform-release-notes#JIRA7.2.xplatformreleasenotes-csvCSVexporter(replacestheExcelHTMLexporter) Is this no longer a concern, since you've given us a solution to re-enable it?

            David Stallard added a comment - - edited

            Confirmed that the excel.enabled property also works in Jira 7.10.2.

            David Stallard added a comment - - edited Confirmed that the excel.enabled property also works in Jira 7.10.2.

            Hannes added a comment -

            How to make Jira export .xls file instead of .html file

            Confirmed, this is working in JIRA7.6 Server, i just tested it.

            But; This is no real XLS File, it is a HTML with a .XLS-file Suffix. And 

            xmlns:x="urn:schemas-microsoft-com:office:excel"

            In the Header..

            Quite the same as saving it as HTML. Only advantage i see is the XLS file-association that you can open it in Excel just double clicking on it...

            Hannes added a comment - How to make Jira export .xls file instead of .html file Confirmed, this is working in JIRA7.6 Server, i just tested it. But; This is no real XLS File, it is a HTML with a .XLS-file Suffix. And  xmlns:x= "urn:schemas-microsoft-com:office:excel" In the Header.. Quite the same as saving it as HTML. Only advantage i see is the XLS file-association that you can open it in Excel just double clicking on it...

            Ignat (Inactive) added a comment -

            Hi everyone,

            I've resolved the the current issue as Not a Bug and put detailed description on suggested approach to get Excel export working in the Current Status field of the issue.

            Cheers,
            Ignat Alexeyenko
            Jira Bugmaster.

            Ignat (Inactive) added a comment - Hi everyone, I've resolved the the current issue as Not a Bug and put detailed description on suggested approach to get Excel export working in the  Current Status field of the issue. Cheers, Ignat Alexeyenko Jira Bugmaster.

            Daniel DuBravec added a comment -

            Any progress on getting this fixed?

            Daniel DuBravec added a comment - Any progress on getting this fixed?

            Przemyslaw Czuj (Inactive) added a comment -

            Hi joerg.geraldy

            In JIRA 7.4 HTML export option was added (link to release notes). It should be much easier to open this type of export in Microsoft Excel.

            Cheers

            Przemyslaw Czuj (Inactive) added a comment - Hi joerg.geraldy In JIRA 7.4 HTML export option was added ( link to release notes ). It should be much easier to open this type of export in Microsoft Excel. Cheers

            Jörg Geraldy added a comment -

            Hello,

            we have the same issues with the new CSV Export - MS Excel Import. 

            To have a Excel-export is business-critical for us too.

            We need a CSV-export with the possibility to set flexibly the value of the field-separator like e.g. ";"

            For us this issue is an very critical issue.

             

            Bye and best regards

             

            Jörg Geraldy

            Jörg Geraldy added a comment - Hello, we have the same issues with the new CSV Export - MS Excel Import.  To have a Excel-export is business-critical for us too. We need a CSV-export with the possibility to set flexibly the value of the field-separator like e.g. ";" For us this issue is an very critical issue.   Bye and best regards   Jörg Geraldy

            Aron Gombas [Midori] added a comment - - edited

            You may want to try the Better Excel Plugin, which avoid all separator-related or encoding problems by exporting native binary XLSX files (not CSV). See this comparison.

            Aron Gombas [Midori] added a comment - - edited You may want to try the Better Excel Plugin , which avoid all separator-related or encoding problems by exporting native binary XLSX files (not CSV). See this comparison .

            Johan Blomgren added a comment -

            Until it has been fixed, I made a chrome plugin:

            https://chrome.google.com/webstore/detail/jira-csv-exporter/bmalhfmkgocjmkeihgjpodlkifpepjbl

            Johan Blomgren added a comment - Until it has been fixed, I made a chrome plugin: https://chrome.google.com/webstore/detail/jira-csv-exporter/bmalhfmkgocjmkeihgjpodlkifpepjbl

              ialexeyenko Ignat (Inactive)
              tsoares Teilor Soares (Inactive)
              Affected customers:
              81 This affects my team
              Watchers:
              80 Start watching this issue

                Created:
                Updated:
                Resolved: