Forum Discussion
Regional CSV Issue
Hi
I was wondering if someone could help with a very annoying CSV file issue.
I am a remote worker based in South Africa working in a finance team for a UK based company.
The company uses Xero as their accounting software, and to import data into xero I need to create CSV columns with data in multiple defined columns (which vary depending on what type of data I am importing)
However, for some reason whenever I download the CSV template direct from xero, populate the columns with the data and save it, when it comes to importing into Xero, I get an error message saying Xero can’t read any of the data.
I have sent this CSV file to my manager, and when he opens it, the column headers in row 1 are all in the right columns, however the data that I have populated in rows 2 and below are all bunched together in column A, with lots of commas separating the different data points.
Likewise, if my manager creates the CSV himself, and then sends me that file, when I open it the same thing happens again (Column headers in row 1 stay in their columns but all data from row 2 and below gets bunched up together in column A)
I am pretty sure it is a delimiter issue, but I have tried changing my regional settings to no avail, as well as using the text to columns button and using comma as a delimiter then resaving, but that still doesn’t work.
Any advice would be greatly appreciated
1 Reply
I think you are right — this is a delimiter and regional settings issue, which is very common when working with CSV files between countries that use different list separators (commas vs semicolons).
In South Africa, your regional settings likely default to using a semicolon (;) as the list separator.
However, in the UK, and in most CSV files created for software like Xero, the standard delimiter is a comma (,).
Option 1: Change List Separator (Recommended for Ongoing Work)
Go to:
Windows Control Panel → Clock and Region → Region → Click "Additional settings…"
In the "List separator" field:
Change from ; to ,
Click OK, then Apply and OK again.
This tells Excel to use commas in CSV files, ensuring compatibility with UK-based tools like Xero.
After changing this, you can open, edit, and save CSVs normally in Excel, and they’ll use commas as expected.
Option 2: Use a Text Editor Instead of Excel to Avoid Misinterpretation
Excel tries to interpret CSVs based on your regional settings, which can break them. If you just want to ensure clean formatting:
Open the CSV in Notepad or Notepad++ (or any plain text editor).
Manually verify that the data is comma-separated.
Edit/save if needed, then import into Xero.
This avoids Excel changing anything behind your back.
What Not to Rely On
- Using “Text to Columns” in Excel can temporarily fix visual issues but doesn’t fix the underlying delimiter used when saving.
- Changing your Excel language/locale doesn’t change the delimiter — that’s tied to Windows system settings, not Excel alone.
Tips for Finance Workflows
- Save a clean CSV export from Excel by choosing "Save As" → "CSV UTF-8 (Comma delimited)"
- Use Power Query if you’re importing data regularly — it can be configured to handle delimiters precisely
- If collaboration across regions is frequent, consider standardizing all team members on comma delimiters for CSV work. The steps were processed with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
