Why does report that was exported to CSV text file display incorrectly in the Excel application?
Environment:
Analytics, Report Export
Oracle B2C Service all versions
Resolution:
Operating System's Regional and Language settings are the most common reason for CSV (Comma Delimited) text file being displayed incorrectly in Excel application.
MS Excel as well as other Office applications heavily depends on the local settings. Specifically, when opening CSV file the region selected list separator will be used by default. Excel will assume that every CSV was saved with that separator. Which is true as long as the CSV was generated with the same regional preset.
Exported CSV files that is generated by Oracle B2C Service use a comma (,) separator and for example if that file is opened on a computer with the European regional settings where decimal separator is a comma and list separator is a semicolon then file will be displayed incorrectly.
Steps below explain on how to change a List Separator for your Regional settings:
- In Microsoft Windows, click the Start button, and then click Control Panel.
- Open the dialog box for changing Regional and Language settings.
- In a Formats tab click "Additional Settings".
- Type a new separator (comma) in the List separator box.
- Click OK twice.
As an option, user may choose to use a Text Import Wizard that is a part of Microsoft Office line of products. Review following link for more information about Text Import Wizard.
