Potential Issues When Opening CSV Files in Excel

Whenever you open the import source file or the exported source file in Excel, you might face issues with some of the field values. This topic explains the various issues that might occur and how to fix them.

Potential Issues

Potential issues that might occur include:

  • Saving ID fields:

    Issue: If you save your CSV file and then reopen it in Excel, IDs and other large numbers are typically displayed in scientific notation: the product of a decimal number and a power of 10. For example, the value 123456789012 is displayed as 1.23457E+11.

    Cause: By default, Excel displays numbers that are greater than 11 characters in scientific notation.

    Workaround:

    1. Highlight the column containing the value.

    2. Right-click and select Format Cells.

    3. In the Format Cells dialog box, select the Category Number and set the decimal places to 0. Click OK.

  • Saving date-related fields:

    Issue: If you save your CSV file and then reopen it in Excel, date-related fields such as PeriodName are typically displayed in yy-mmm format. For example, the value 12-16 is displayed as 16-Dec.

    Cause: By default, Excel displays date-related numbers in yy-mmm format.

    Workaround:

    1. Highlight the column containing the value.

    2. Right-click and select Format Cells.

    3. In the Format Cells dialog box, select the Category Custom and enter the type mm-yy or any other date format of your choice. Click OK.

  • Saving numeric values that have more than 15 digits:

    Issue: If your CSV file has a value with more than 15 digits, such as 1234567890123456, then Excel displays it as 1234567890123450 when the file is opened. Excel replaces the digits beyond 15 with zeroes.

    Cause: Excel supports only 15 digits of precision.

    Workaround:

    Method 1

    1. Highlight the column containing the value.

    2. Right-click and select Format Cells.

    3. In the Format Cells dialog box, select the Category Text and click OK.

    4. Enter the value again in the cell.

    Method 2

    1. Highlight the cell containing the value.

    2. On the Formula Bar, precede the value with an apostrophe. For example, type the value '1234567890123456 and press enter.