Opening Excel files with the correct CSV list separator

In some regions (Europe for example) Excel files downloaded from the Harvest Admin in the format .CSV (comma delimited) will not open correctly and the data within will be displayed incorrectly.

This is due to regional Excel settings that have default list separator options where files will either be read with a comma separator or semicolon separator. In Europe, the default is for the semicolon to be a delimiter instead of a comma.

Below are methods for both Windows and MacOS to change the default list separator:

Windows

  1. Open the Windows Start Menu and click Control Panel
  2. Open the Regional and Language Options dialog box
  3. Click the Regional Options tab
  4. Click Customize/Additional settings (Windows 10)
  5. Type a comma into the 'List separator' box (,)
  6. Click 'OK' twice to confirm the change

Note: This only works if the 'Decimal symbol' is also not a comma. If you do not want to change this setting, there is another method to opening comma delimited files below.

MacOS

  1. Go to System Preferences
  2. Open the Language & Region pane and go to the Advanced option
  3. Change the 'Decimal Separator' to one of the below scenarios

For MacOS, if the Decimal Separator is a period (.) then the CSV separator will be a comma.
If the Decimal Separator is a comma (,) then the CSV separator will be a semicolon.

 

Alternative method to open CSV files

If you do not want to change the default list separator or decimal symbols, then the below method will be an alternative for the meantime:

  1. Open a new empty spreadsheet in Excel
  2. Go to the Data tab and select 'From Text'
    1.png
  3. Select the file you want to open
  4. Choose the 'Delimited' option and click Next
    2.png
  5. Select the correct delimiter which will display the metadata correctly in the Preview pane below
    3.png
  6. Make your changes to the file and save as .TXT (tab delimited) so that the file is delimited by tabs instead of a variable character.

Note: Instructions may differ slightly on MacOS

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.