Number Values in CSV Exports Display Incorrectly When User Language Is Set to Spanish

Summary

Symptoms

When a user exports issues from Jira to a CSV file using the "CSV (Current Fields)" option, number values are formatted unexpectedly when the user’s language is set to Spanish. For example: A field with the value 100,000 in the Jira UI appears as 100.000 in the CSV file.

Large numbers may also appear in scientific notation (e.g., 1.000E5 for 100,000) when viewed in spreadsheet software.

This issue is noticeable when opening the CSV file in applications like Microsoft Excel or Google Sheets.

Diagnosis

1. Verify User Language Settings:

   • Check the user’s profile in Jira to confirm their language is set to Spanish.

   • In Spanish-speaking regions, number formatting typically uses:

     • Comma (,) as the decimal separator.

     • Period (.) as the thousands separator.

   • This differs from English formatting (e.g., 100,000 for one hundred thousand).

2. Test Number Display in Jira UI:

   • Create or view a custom field of type Number Field in Jira.

   • Set a value (e.g., 123131536):

     • With default (English) language: Displays as 123,131,536.

     • With Spanish language: Displays as 123.131.536.

3. Export and Inspect CSV:

   • Export the issue to CSV using "CSV (Current Fields)".

   • Open the CSV file in a text editor to check the raw data:

     • Large numbers may appear in scientific notation (e.g., 1.23131536E8 for 123,131,536).

   • Open the CSV in spreadsheet software (e.g., Excel or Google Sheets) and note how numbers are interpreted.

4. Check Spreadsheet Software Settings:

   • The display of numbers in the CSV file depends on the locale or number format settings of the spreadsheet application.

   • Scientific notation or incorrect separators (e.g., 100.000 instead of 100,000) may appear if the locale does not match the user’s language.

Cause

• Locale-Based Formatting in Jira UI:

  • Jira adjusts the display of numbers in the UI based on the user’s language settings. For Spanish, it uses a period (.) as the thousands separator and a comma (,) as the decimal separator.

• CSV Export Behavior:

  • When exporting to CSV, Jira does not preserve the UI formatting (e.g., 123.131.536). Instead, it outputs numbers in a locale-independent format, often using scientific notation for large values (e.g., 1.23131536E8).

  • Spreadsheet software interprets this raw data based on its own locale settings, which may not align with the user’s expectations (e.g., Spanish formatting).

• Spreadsheet Misinterpretation:

  • If the spreadsheet application’s locale is not set to Spanish, it may misinterpret the number format, leading to confusion (e.g., displaying 100.000 as a decimal instead of 100,000).

Solution

To resolve the issue and ensure number values in CSV exports are displayed correctly:

1. Understand CSV Export Format:

   • Recognize that Jira exports numbers in a standardized, locale-independent format (e.g., 1.23131536E8 for 123,131,536).

   • Scientific notation is used for large numbers, where:

     • Base number (e.g., 1.23131536) is multiplied by 10 raised to the exponent (e.g., E8 = 10⁸).

2. Adjust Spreadsheet Software Settings:

   • Microsoft Excel:

     • Open the CSV file using the "Text Import Wizard" (or "Get Data" in newer versions):

       1. Set the file’s language/locale to Spanish.

       2. Specify the column containing numbers and set the format to "Number" with the appropriate separators (comma for decimals, period for thousands).

     • Alternatively, after opening, select the column, go to "Format Cells" > "Number," and choose the Spanish locale.

   • Google Sheets:

     • Open the CSV file and select the column with numbers.

     • Go to Format > Number > Custom number format and set it to #,##0.00 (adjust based on desired decimal places).

     • Change the spreadsheet’s locale to Spanish via File > Settings > Locale > select a Spanish-speaking country (e.g., Spain).

3. Verify Correct Display:

   • After adjusting the locale, confirm that numbers display as expected (e.g., 123.131.536 for Spanish formatting).

   • For smaller numbers like 100,000, ensure it appears as 100.000 in the Spanish format.

4. Educate Users:

   • Inform users that the CSV export format is not influenced by their Jira language settings. The display depends on how the spreadsheet software interprets the data.

   • Recommend setting the spreadsheet locale to match their language preference (e.g., Spanish) for consistency.

In case any assistance is required from Atlassian please get in touch through https://support.atlassian.com/contact/.