Troubleshooting CSV file formats

Depending on your Excel's regional settings, your default delimiter/separator may either be set to semicolons (;) or commas (,) to separate items in a CSV file. This can either cause file upload issues or combine all the field values into Column A (see example below).

To remedy this issue, you can either change your delimiter or change your Excel regional settings: 

Option 1 - How to change your delimiter

• Using the "From Text" feature in Excel
• Adding "sep=;" or "sep=," to the CSV file
  
  

Option 2 - How to change your Excel regional settings

• Changing your regional settings (Windows)
• Changing your regional settings (Mac)

Option 1 - How to change your delimiter

Using the "From Text" feature in Excel

1. Open the Excel file that you're trying to change in terms of data formatting. These steps will apply to both Mac and PC versions of Excel.
   
   
   
2. Click the Data tab, then From Text.
   
   
   
3. Select the CSV file that has the data clustered into one column.
4. Select Delimited, then make sure the File Origin is Unicode UTF-8.
   
   
   
5. Select Comma (this is Affinity's default list separator). The preview will show the columns being separated.
6. Finally, click Finish.
   
   
   
7. Remember to Save your file so it keeps the new format.

Adding "sep=;" or "sep=," to the CSV file

When you have a CSV that is separated by semicolons (;) and your system/Excel default is commas (,), you can add a single line to tell Excel what delimiter to use when opening the file. Here are the steps you should follow:

1. Open your CSV using a text editor.
   1. Windows: NotePad (notepad.exe)
   2. Mac: TextEdit (textedit.app)
2. Press Enter/Return to create a new line at the very top, and add sep=; if the separator used in the CSV is a semicolon (;), or sep=, if the separator is a comma (,). 
3. Save your changes (command + S or control + S), then re-open in any Spreadsheet app.
   
   Note: This newly added line will not show up when opening the file in Excel. Once you've opened up the Excel file (and the formatting looks good), you can re-save the file (if your default separator is a comma) so the "sep=" line is no longer present, which will allow for imports.

Option 2 - How to change your regional settings

Your regional settings are set by the country/ region in which you reside. By changing your settings to English (United States), you will change your default delimiter to comma (,) to be in compliance with the .CSV format that Affinity uses for imports and exports (also see Microsoft's article on how to change your regional settings).

Changing your regional settings (Windows)

1. If you're using a PC (Windows), open the Windows Start Menu and click Control Panel.
   
   
2. Click Region.
   
   
   
3. Then, click on Additional settings...
   
   
   
4. Now, make sure that the Decimal symbol field is set to period (.) and that the List separator field is set to comma (,).
   
   
   
5. Last but not least, click OK to save your changes.

Changing your regional settings (Mac)

1. If you're using a Mac, click on System Settings.
   
   
   
2. Click the General tab, then click Language & Region.
   
   
   
3. Change the Number Format to 1,234,567.89 and you're all set.