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 some ways to open files without changing the system settings:
Using "Data - From Text" to open files
Mac/Windows
- Open a new Excel sheet.
- 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 document!
Other tips and tricks
Adding "sep=;" or "sep=," to the CSV
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. To do this:
- Open your CSV using a text editor.
- Windows: NotePad (notepad.exe)
- Mac: TextEdit (textedit.app)
- Skip a line at the top, and add sep=; if the separator used in the CSV is a semicolon (;), or sep=, if the separator is a comma (,).
- Save, and re-open the file.
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.