23

When saving/opening CSV files in Excel, the default separator is a semicolon ;, as I need it to be a comma , I tried to change it by changing the Language and Regional settings following several other posts about this issue (i.e. How to get Excel to interpret the comma as a default delimiter in CSV files?).

However, after changing the List separator in those settings, my Excel keeps saving CSV files with a semicolon. Is it because Excel was installed while my list separator was set as a semicolon? or is there another setting I am missing here?

2
  • Is the problem in reading existing CSV files or creating one using comma as the delimiter?
    – fixer1234
    Oct 28 '14 at 15:33
  • Both, I have already done what @Raystafarian mentioned before saving a new file. My regional setting is correct. Then I open Excel and save as CSV and when I open the file in notepad I can see that it used a semicolon. If I open in Excel it does load correctly because Excel still uses a ; for separating the columns.
    – Sandra
    Oct 28 '14 at 17:27
36

I found the problem. My decimal symbol in the Regional settings was also a comma (European) so, even when my List separator was a comma, the CSV was saved with semicolons. Now I changed my decimal symbol to a point and now the CSV file is created correctly with commas as separators. I tested this twice and now know that there must be an exception: if the decimal symbol is a comma, then the list separator will be a semicolon even is set otherwise.

1
  • 13
    Thank you for taking the time to update the post with an answer. Oct 28 '14 at 18:14
5

For saving

You need to adjust the list separator in language and regional settings prior to saving the file -

enter image description here


For opening

Go to Data - Get External Data - From Text

enter image description here

select the file

Select delimited, press next enter image description here

and select the delimiter comma

enter image description here

Click finish

enter image description here


Or, pull it in and then use text to columns see here

3
  • If I read the question right, it looks like the problem is creating (saving), a delimited file using commas for the delimiter (2nd paragraph).
    – fixer1234
    Oct 28 '14 at 15:29
  • @fixer1234 yeah that's how I read it at first. But then I thought the first paragraph was the important one. I've no idea which is correct. Either way, I have no solution for the creation other than what the OP mentioned they've already tried. Oct 28 '14 at 15:30
  • Yes, I did change the regional settings before saving the file. It didn't help. When I receive CSV files from other people which are delimited with a comma, then I use your method for importing the data correctly, then I save and it goes to a semicolon. It's annoying because every time I need to go to notepad and replace all semicolons with commas before using my CSV as an import file.
    – Sandra
    Oct 28 '14 at 17:27
0

Here is a very nice macro that works quite well:

Sub saveCSV()
File = Application.GetSaveAsFilename(InitialFileName:="file.csv", FileFilter:="CSV (*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=File, FileFormat:=xlCSV, CreateBackup:=False, local:=False
End Sub
0

This is a bit old, but I ran into the same problem:
Had a CSV delimited with semicolon (;). System separator settings (Windows) also set to semicolon.

  • If I opened the CSV manually, the file was separated correctly.
  • If I let VBA open it, it separated it incorrectly with a comma.

Using Delimiter:=";" apparently only works with .txt.

HOWEVER, using Local :=True solved the problem for me:

Workbooks.Open(Filename:=fullpath, Local:=True)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.