How to open CSV files with the correct delimiter/separator
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.
Here are two ways to open files without needing to change your Excel settings:
Using the "From Text" feature in Excel
- 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.
- Click the Data tab, then From Text.
- Select the CSV file that has the data clustered into one column.
- Select Delimited, then make sure the File Origin is Unicode UTF-8.
- Select Comma (this is Affinity's default list separator). The preview will show the columns being separated.
- Finally, click Finish.
- 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:
- Open your CSV using a text editor.
- Windows: NotePad (notepad.exe)
- Mac: TextEdit (textedit.app)
- 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 (,).
- 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.