In some countries, the default delimiter value is a semicolon (;) instead of a comma (,), even when selecting Comma Separated Values (.csv) in Excel. This can apply to French-Canadian customers, as well as some European regional settings.
Changing Your Computer's Regional Settings:
If .CSV isn't a common use case for you, you may have the option to update your regional delimiter in your computer's settings. This default value should already be there and will affect other software too. This step will change the default value used by your applications for a delimiter value. You'll need to restart your computer for the changes to take effect.
To Change the Default Delimiter Setting on Windows:
1. Open the Windows Start menu and click Control Panel
2. Open the Region and Language dialog box
3. Click the Regional Options tab
4. Click Customize/Additional Settings (Windows 10)
5. Type a comma in the "List Separator" box (,)
6. Click "OK" twice to confirm the change
To Change the Default CSV Setting on Mac:
1. In Spotlight search, search for "Language & Region"
2. Select "Advanced"
3. Make sure the number separators for grouping are set to comma (,)
To Specify the Separator Directly in the CSV File:
To have Excel read a CSV file using a specific field separator, you can specify the separator directly in that file.
Open your file in any text editor. For example, Notepad or TextEdit
Type this string before any other data: sep=,
Open your text file in Excel, either from Excel itself or your file explorer
Can't Change the Computer's Delimiter
If You Already Have a CSV File with the Wrong Delimiter.
Fix the CSV file so it has the correct delimiter.
Edit the file you want in Excel and save it as .CSV. This will still have the wrong delimiter.
Go to the File tab
Click Save As
In the Type box, choose to save your Excel file as CSV (Comma delimited)
DO NOT OPEN or convert the file in Excel.
You'll need to open the file in a text editor (for example, Notepad if available). Notepad++ is a good free option. This will show the text version of the file, displaying the semicolons.
Open the .CSV file in the text editor.
Edit menu and Find and Replace (it may be called Replace in your text editor)
Find ";" and replace with [Blank] (nothing in the replace box)
Choose Replace All
Save your document, don't rename it
Other Considerations
This should give you a CSV file with comma-separated values ready to import correctly.
Note: if you get an error importing locations, you may need to format the address import without commas in the address field. Commas are typically ignored during standard .CSV import, but you may need to remove them. In Excel, you can find "," and replace it with nothing to remove them from addresses. Then repeat the steps to save the CSV.