FAQ: How to work with CSV files when comma delimiter is not recognised

Applies to: Risk Assessment Platform

Question

• How do you use CSV files when regional settings use semi-colons as delimiters instead of commas?
• Why are the columns lost when opening a CSV file in Excel?
• Importing a CSV file fails with the error "Invalid CSV header format. Column count 1 not matching expected n" (where 'n' varies by content type).

Answer

The Risk Assessment Platform expects the list separator in CSV files to be a comma.

However, Excel uses different list separators depending on your regional settings. This is because Microsoft Excel uses the List separator defined in Windows Regional settings to handle .csv files.

In some other countries, such as Australia or North America, the default list separator is a comma, so you get CSV comma delimited.

In other countries, such as those in Europe and Africa, a comma is reserved for the decimal symbol, and the list separator is set to something else, generally the semicolon.

If you are in a country that does not use a comma as the list separator, you may experience the following issues when downloading CSV files from the platform or trying to import CSV files:

• Downloaded CSV files may not display columns properly in Excel
• Importing a CSV file (e.g. to create App Setup content) may fail with an error. 

What to do

• To get Excel to put CSV file contents in columns, see Change delimiter when importing CSV to Excel
• To get Excel to save the CSV file with a comma separator, see Change separator when saving Excel file as CSV.

Change delimiter when importing CSV to Excel

There are several different ways to import CSV files into Excel. The method you opt for will determine how you change the delimiter. These steps will allow CSV files from the platform to display correctly in Excel.

Method 1: Indicate the separator directly in the CSV file

You can specify the separator directly in the CSV file before opening it in Excel. For this, open your file in any text editor, say Notepad, and type the following string before any other data: sep=,

Save the file and open it in Excel like you normally would.

An example that explicitly indicates the field separator is a comma:

Method 2: Choose the delimiter in Text Import Wizard

In Excel, select File > Open > and select the CSV file. Excel should recognise the delimiter doesn't match the default, and present the Text Import Wizard.

In the wizard, select Delimited at Step 1, Comma at Step 2, and at Step 3, press Finish.

 

Change separator when saving Excel file as CSV

When you save a workbook as a .csv file, Excel separates values with your default List separator. To force it to use a different delimiter, proceed with the following steps:

1. Click File > Options > Advanced.
2. Under Editing options, clear the Use system separators check box.
3. Change the default Decimal separator. As this will change the way decimal numbers are displayed in your worksheets, choose a different Thousands separator to avoid confusion.

To convert an Excel file to CSV comma delimited, set the default decimal separator to a decimal. This will get Excel to use a comma for the List separator (CSV delimiter):

• Set Decimal separator to decimal/period (.)
• Set Thousands separator to comma (,)