Troubleshooting CSV file formats

Depending on your Excel's regional setting, your default delimiter/separator may either be using semicolons (;) or commas (,) to separate items in a CSV file. This can either cause file upload issues or cluster all the field values into column A because exported files from Affinity use commas (,) as the default delimiter/separator (see example below).

To remedy this issue, you can either change your delimiter or change your 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 regional settings

• Changing your regional settings
  
  

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

Changing 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 commas (,) to be in compliance with the .CSV format that Affinity uses for imports and exports. 

Please see Microsoft Excel's article on how to change your regional settings.